June 25, 2008

Combine Multiple Rows into One Row using SQL Server

Imagine you have a column like this:

Numbers
---------
One
Two
Three
Four
Five

The output you desire is to combine all the rows and put it as one row similar to the following:

OneTwoThreeFourFive


Let us see how to do it:

-- Sample Script to create the table and insert rows
-- By SQLServerCurry.com

CREATE TABLE #Temp
(
[Numbers] varchar(40)
)
INSERT INTO #Temp VALUES('One');
INSERT INTO #Temp VALUES('Two');
INSERT INTO #Temp VALUES('Three');
INSERT INTO #Temp VALUES('Four');
INSERT INTO #Temp VALUES('Five');



-- Query to combine multiple rows into one

DECLARE @str VARCHAR(100)
SELECT @str = COALESCE(@str + '', '') + [Numbers]
FROM #Temp
Print @str

You can also achieve the same result using STUFF

SELECT DISTINCT STUFF( (SELECT '*' + Numbers from #Temp FOR XML PATH('')),1,1,'') as Numbers FROM #Temp

Update: Here's another solution if you want to combine and output multiple rows as CSV - SQL Server: Combine Multiple Rows Into One Column with CSV output


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

20 comments:

Anonymous said...

Nice! I like the XML technique :)

saravanan said...

REALLLY NICE. Thanks....

Anonymous said...

just what i needed!

Anonymous said...

Brilliant thanks!

Anonymous said...

briliant !
what if we only want the distinct values ?
say... we have 2 values of "two" and we only want to print One|Two|Three|Four|Five. not One|Two|Two|Two|Three|Four|Five.
where do i have to place the distinct keyword ?

madhu said...

thanks working perfect , mainly xml stuff

Anonymous said...

super..thank you

Dinesh said...

Thanks a lot. Your solution helped me in making a query.

Ramya Raj said...

Its good...
But wat if i want to use integer instead of varchar???

This wud not work...
Wat to do in that cae??

Shyam said...

but what do i have to do in case that strings are replaced with numbers??

Madhivanan said...

Ramya raj and shyam,

You need to convert the number to varchar

Anonymous said...

super boss :)

Valentin said...

This will not work when the sql statement contains a JOIN.

For example:
DECLARE @str VARCHAR(100)

SELECT
SQLCUS_CRM.DESCRIPTION,
@str = COALESCE(@str + '|', '') + cast(cuscrmid as varchar(10))
FROM SQLCUS_CRM
LEFT JOIN SQLCUS_CRMUSER ON SQLCUS_CRMUSER.CUSCRMID = SQLCUS_CRM.ID
Print @str

Will throw an error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Is there any solution for this ?

Anonymous said...

Anonymous said...
November 13, 2008 5:14 AM
briliant !
what if we only want the distinct values ?
say... we have 2 values of "two" and we only want to print One|Two|Three|Four|Five. not One|Two|Two|Two|Three|Four|Five.
where do i have to place the distinct keyword ?

Never got a reply for this.

Madhivanan said...

Anonymous, here are the methods that return unique values

DECLARE @str VARCHAR(100)
SELECT @str = COALESCE(@str + '|', '') + [Numbers]
FROM (select distinct [numbers] from #Temp) as t
Print @str

SELECT DISTINCT STUFF( (SELECT distinct '*' + Numbers from #Temp FOR XML PATH('')),1,1,'') as Numbers FROM #Temp

Pramod Kasi said...

I have two COLUMNS

Col1 | Col2
A | A1
A | A2
A | A3
A | A4
A | A5
B | B1
B | B2
B | B3
B | B4
C | C1
C | C2
C | C3


I need my results TO be

Col1 | Col2
A | A1,A2,A3,A4,A5
B | B1,B2,B3,B4
C | C1,C2,C3

How will I get this. I am in learning phase of T-SQL sorry if its too basic question to paste, and I am using sql 2008 varchar (40). I need some here please.
I tried some options with Group by and using STUFF but I am not getting the exact one.

Suprotim Agarwal said...

Pramod: Your requirement can be achived in three ways: FORXML, a PIVOT operator or CLR aggregation function. I will use FORXML and post the answer in a couple of hours as soon as I have access to my machine.

Stay tuned!

Suprotim Agarwal said...

Pramod: Here's the query.

SQL Server: Combine Multiple Rows Into One Column with CSV output


HTH

chi chin said...

how if update 2 table with multiple row
table 1
id,
name,
address

table 2
id,
id_one
hobby
phone

sampel if someone have 1 name but have 2 hobby n phone

how update table 2 ???

Anonymous said...

hi I have one table in which have employee_id, manager_id and Employee_Name.

Employee 1st Have no Manager and employee 1st is the Manager of Employee 2nd.
and now show the result in table in which field is employee_id, employee_name and Manager_Name