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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

20 Responses to "Combine Multiple Rows into One Row using SQL Server"
  1. Anonymous said...
    August 18, 2008 at 8:26 PM

    Nice! I like the XML technique :)

  2. saravanan said...
    September 25, 2008 at 12:17 AM

    REALLLY NICE. Thanks....

  3. Anonymous said...
    September 25, 2008 at 10:39 AM

    just what i needed!

  4. Anonymous said...
    October 30, 2008 at 10:11 AM

    Brilliant thanks!

  5. Anonymous said...
    November 13, 2008 at 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 ?

  6. madhu said...
    January 1, 2009 at 11:28 PM

    thanks working perfect , mainly xml stuff

  7. Anonymous said...
    July 15, 2009 at 11:36 AM

    super..thank you

  8. Dinesh said...
    March 19, 2010 at 10:24 AM

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

  9. Ramya Raj said...
    May 19, 2010 at 11:31 PM

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

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

  10. Shyam said...
    September 15, 2010 at 2:07 AM

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

  11. Madhivanan said...
    September 15, 2010 at 2:18 AM

    Ramya raj and shyam,

    You need to convert the number to varchar

  12. Anonymous said...
    September 27, 2010 at 7:38 AM

    super boss :)

  13. Valentin said...
    October 20, 2010 at 6:00 AM

    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 ?

  14. Anonymous said...
    March 3, 2011 at 4:49 AM

    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.

  15. Madhivanan said...
    March 3, 2011 at 5:33 AM

    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

  16. Pramod Kasi said...
    March 3, 2011 at 9:27 AM

    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.

  17. Suprotim Agarwal said...
    March 3, 2011 at 10:15 PM

    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!

  18. Suprotim Agarwal said...
    March 4, 2011 at 1:08 AM

    Pramod: Here's the query.

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


    HTH

  19. chi chin said...
    August 1, 2012 at 2:23 AM

    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 ???

  20. Anonymous said...
    July 28, 2013 at 1:21 AM

    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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions