Concatenate Column Value From Multiple Rows Into A Single Column

Feb 27, 2008

Hello,

I need to concatenate a column from multiple rows into a single column in a new table.

How can I do this?

SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR


Need to have

SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR


THANK YOU

View 10 Replies


ADVERTISEMENT

Concatenate Multiple Rows Into One Column?

May 7, 2008

Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:TableParent (ASSIGNNUM (PK), DESC, STARTDATE)TableChild (ASSIGNNUM (FK), EMPLOYEENUM)There could be multiple employees for each assignment. Sample data:TableParent1....First Assignment....05/01/20082....Second Assignment...05/03/20083....Third Assignment....05/07/2008TableChild1....553422....334562....523433....352253....451213....11553I would like the query result to look like this:1....First Assignment....05/01/2008....553422....Second Assignment...05/03/2008....33456,523433....Third Assignment....05/07/2008....35225,45121,11553Any suggestions would be appreciated!

View 5 Replies View Related

Concatenate Multiple Rows Into One Column?

May 7, 2008

Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:

TableParent (ASSIGNNUM (PK), DESC, STARTDATE)
TableChild (ASSIGNNUM (FK), EMPLOYEENUM)

There could be multiple employees for each assignment. Sample data:

TableParent
1....First Assignment....05/01/2008
2....Second Assignment...05/03/2008
3....Third Assignment....05/07/2008

TableChild
1....55342
2....33456
2....52343
3....35225
3....45121
3....11553

I would like the query result to look like this:

1....First Assignment....05/01/2008....55342
2....Second Assignment...05/03/2008....33456,52343
3....Third Assignment....05/07/2008....35225,45121,11553

Any suggestions would be appreciated!

View 12 Replies View Related

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

Mar 3, 2008



Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760

i want output for the above as:

Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.

View 8 Replies View Related

Combine Multiple Rows To Single Column

Jul 8, 2014

With the below query iam able to retrieve all the tables invloved in a stored proc. But, what I want to display the table names as comma separated list for each table.

;WITH stored_procedures AS (
SELECT o.id,
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT id,proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

View 6 Replies View Related

Multiple Rows Into A Single Row And Combining Column Values?

Apr 6, 2014

I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.

Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.

EXAMPLE: All in one row.

ProgramID | AttributeNames
887 | Studydesign, Control Groups, Primary Outcomes.

I have attached an image of the SQL VIEW that I need to modified so it does this.

THE QUERY:

SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID,
dbo.tblAttributes.AttributeName
FROM dbo.tblProgramAttributes INNER JOIN
dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID
WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%')
ORDER BY dbo.tblProgramAttributes.ProgramID DESC

View 5 Replies View Related

T-SQL (SS2K8) :: Convert Multiple Rows In Single Column

Sep 4, 2014

Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.

Below is sample of data:

create table #Temp(depID int, Name varchar(50),LocationHead varchar(50))
insert into #temp values(1,'test','head1')
insert into #temp values(1,'test','head2')
insert into #temp values(1,'test','head3')
insert into #temp values(2,'test1','head1')
insert into #temp values(2,'test1','head2')

Required output

depID Name LocationHead
1test *head1,*head2,*head3
2test1 *head1,*head2

View 2 Replies View Related

SQL Server 2008 :: Split Single Row Into Multiple Rows Based On Column Value (quantity)

Jan 30, 2015

Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY

Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?

View 1 Replies View Related

Concatenate Query From Single Column

Feb 22, 2007

I have a query that I'm stumped on. The data has about 6000 records, of which about 460 of those have distinct dealer names and ids.

I am trying to condense this data so that there is only one record for each dealer, but my 3rd column has different values which is why some dealers have only 2 records, and others may have 10.

I know how to select distinct values, but what I want is to return a list of comma separated values for my 3rd column which consists of all the unique values from that 3rd column for that dealer.

For instance, say I have a dealer with 8 records. They have a unique ID and Dealer Name, but the 8 records appear because the dealer has 8 entries for its activity. The activities may or may not be unique.

So for example I have dealer ABC who has an id of 54, their name is ABC, but for their activites they have 8 entries (apple, orange, banana, pear, apple, banana, mango, peach).

I wnt to be able to select the distinct id, name, and distinct activities so that when my query is returned and displayed the record appears as such:

"54","ABC","apple, orange, banana, pear, mango, peach"

Does that make sense?

View 4 Replies View Related

Concatenate Values From Same Column But Different Record In Single Row

Mar 26, 2008

Hi, I have a difficult case that I need to solve. I will try to be the very clear explaining my problem:


I have a sql query which brings me many records.
This records have a column in common which have the same value (COL1)

There is a second column (COL2) which has different values bewteen these records.
I need to concatenate values from the second column in records with same value in COL1. And I need only one record of the ones that have the same values. If two records have the same COL1 value, only one row should be in my result.

Let me give you an example:
COL1 COL2
RECORD1 1-A HHH
RECORD2 1-A GGG
RECORD3 1-B LLL
RECORD4 1-B MMM
RECORD4 1-B OOO
RECORD5 1-C NNN

Me result should be:


COL1 COL2
RECORD 1-A HHHGGG
RECORD 1-B LLLMMMOOO
RECORD 1-C NNN

It is clear what I need? I dont know if I can solve it through sql or any function inside SSIS.
Thanks for any help you can give me.

View 5 Replies View Related

Stored Procedure To Concatenate Column Value Sin A Single Cell?

Jan 8, 2007

HiI want to achieve the following transformation of data using a storedprocedure.Sourcecol1 col2(varchar)-------------------------1 1.11 1.22 2.12 2.22 2.3=================Desired Resultcol1 col2(varchar)--------------------------1 1.1 | 1.22 2.1 | 2.2 | 2.3=====================Thanks in advance. :)- Parth

View 12 Replies View Related

Reporting Services :: Concatenate 24 Columns With Semicolon Delimited Into Single Column

Sep 17, 2015

How do i concatenate 24 columns with semicolon delimited into a single column without getting data conversion error ....

View 2 Replies View Related

Concatenate All Binary Columns Into Single Binary Column?

May 22, 2014

Server is SQL 2000

I have a table with 10 rows with a varbinary column

I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)

example as follows

TableA

Column1 Column2 Column3
aaa 001 <some binary value>
aaa 002 <some binary value>
aaa 003 <some binary value>
aaa 004 <some binary value>
aaa 005 <some binary value>

desired results in TableB

Column1 Column2
aaa <concatenated value of above binary columns>

View 9 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

Aug 22, 2007

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View 1 Replies View Related

Adding A Single Change To All Rows In A Column

Jan 16, 2002

View 1 Replies View Related

Adding A Single Change To All Rows In A Column

Jan 16, 2002

We just changed over our phone system and the new system uses all of the old extensions except it adds a 1 to the beginning of them. I know that there is a relatively simple way to update my phone extension column to show this, but I can't for the life of me remember what I need to do. Any help?

View 2 Replies View Related

Comparing For Multiple Value With Single Column

May 10, 2001

Hi,
i need to select from a table transact where one of the coulmn values has to be equal to (1and 2 and 3).

e.g: column in (1,2,3) would give me what "OR" would do,

View 1 Replies View Related

Multiple Values For Single Column In Where Clause

Jun 25, 2004

how does one specify multiple values for a single column in a where clause?

example:

SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')


if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:

View 2 Replies View Related

To Alter Multiple Column With Single Statement

May 5, 2008

It is possible to alter multiple columns within a single alter table statement?
I have got the following URL that tells it is not possible to alter multiple columns within in signle alert table statement.
http://www.blogcoward.com/archive/2005/05/09/234.aspx[^]
Does anyone know about that?


Thanks,
Mushq

View 4 Replies View Related

Making Single Column Value To Multiple Columns

Mar 4, 2008

I have a table which has single column like this.


REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT

REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT

REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT

I want to put this in a new table in individual columns

Col1 col2 col3 col4 col5 col6 col7 col8 col9

REP5426 02-28-08 0592 00100028 CAFE 00205415 23.77 A O INPUT
REP5426 02-28-08 0592 00100028 CAFE 00580910 475.58 A O INPUT

REP5426 02-28-08 0592 00100028 CAFE 00800840 -13.32 A O INPUT

REP5426 02-28-08 0592 00100028 CAFE 00205416 23.77 A O INPUT

How to do this.
Thanks.

View 2 Replies View Related

Transact SQL :: Single Row Multiple Columns Into One Column

Jun 4, 2015

I have a requirements to make a single column using a date from multiple columns. below my DDL and sample.

Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)

[code]....

View 10 Replies View Related

How To Return Multiple Columns As Single Column

Mar 9, 2008

I have FirstName,LastName columns in the database.I need to return FirstName,LastName as Name to client(as a single column).

View 3 Replies View Related

How Do I Return All Rows Value In A Single Column Delimit With Comma Separation?

Oct 23, 2007

Dear all,

I have a table like:

State -- Customer
TN -- AAA
TN -- AAA1
TN -- AAA2
Delhi -- BBB
Delhi -- BBB1
Delhi -- BBB2
Mumbai -- CCC
Mumbai -- CCC1
Mumbai -- CCC2
Maharashtra -- DDD
Maharashtra -- DDD1
Maharashtra -- DDD2

I want to show the output in a single query like:

State -- Customers
TN -- AAA, AAA1, AAA2
Delhi -- BBB, BBB1, BBB2
Mumbai -- CCC, CCC1, CCC2
Maharastra -- DDD, DDD1, DDD2


How do i do this in a single query

Can Pivot query will help in this situation. Please explain with a sample query

Thanks
gopalan@sofist.com
okugops@hotmail.com

View 4 Replies View Related

How Can I Combine Values Of Multiple Columns Into A Single Column?

Oct 8, 2007



Suppose that I have a table with following values
Table1
Col1 Col2 Col3
-----------------------------------------------------------
P3456 C935876 T675
P5555 C678909 T8888

And the outcome that I want is:
CombinedValues(ColumnName)
----------------------------------------------
P3456 - C935876 - T675
P5555 - C678909 - T8888

where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-'
So is there any way to achieve this?

View 1 Replies View Related

SQL Server 2012 :: Concatenate Multiple Rows In Multiple Columns

Aug 5, 2014

I concatenate multiple rows from one table in multiple columns like this:

--Create Table
CREATE TABLE [Person].[Person_1](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED

[Code] ....

This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?

View 1 Replies View Related

T-SQL (SS2K8) :: Replace Multiple Characters With Single Character In A Column?

Jun 21, 2012

I am trying to replace all special characters in a column with one special character.

Example:

Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"

I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.

I am expecting the following results:

Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<

View 9 Replies View Related

SQL Server 2012 :: How To Add Column To Multiple Table Using Single Script

Feb 12, 2015

I am looking a script which allow me add single coilumn to multiple table of my database.

For Example :-

I am having 4 table

1-Emp , 2-Dept , 3-Location , 4-Salary like this I have around 100 of table

Now I want to run below command to add column Rowchecksum in all table where table name start with Archivebbx keywords.

Alter table EMP
Add Rowchecksum varbinary(8000)

View 1 Replies View Related

SQL Server 2012 :: Splitting A Single Column Into Multiple Columns?

Mar 3, 2015

I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.

For Eg:

Description
APPEARANCE UNIT
BDV KV
DENSITY KG/L

View 9 Replies View Related

Transact SQL :: To Show Multiple Column In Multiple Rows

Aug 14, 2015

I have the following  database structure

Stock        Depth41     Depth12    Depth34
AAA            1              2              1
BBB             2            2               4

How can I show  Each Depth column as seperate row

AAA          1
AAA          2
AAA          1  as follows

View 3 Replies View Related

Want 2 Store Multiple Room Numbers Of A Hotel In A Single Column Using SQL Server2k.

Jan 15, 2004

Hi!
Want 2 store Multiple room numbers of a hotel in a single column using SQL Server2k.

Can anybody tell me how to do it??

Thanx.

View 2 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 12, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???





View 1 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 16, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):



1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.



Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???



2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???


thanks,

Ran Kizi

View 3 Replies View Related

Concatenate Multiple Rows In One Row

Mar 11, 2008

i have a table

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved