SQL SERVER – Grouping by Multiple Columns to Single Column as A String

One of the most common questions I receive in email is how to group multiple column data in comma separate values in a single row grouping by another column.

I have previously blogged about it in following two blog posts. However, both aren’t addressing the following exact problem.

The question comes in many different formats but in following image I am demonstrating the same question in simple words. This is the most popular question on my Facebook page as well. (Example)

Here is the sample script to build the sample dataset.

CREATE TABLE TestTable (ID INT, Col VARCHAR(4))
GO
INSERT INTO TestTable (ID, Col)
SELECT 1, 'A'
UNION ALL
SELECT 1, 'B'
UNION ALL
SELECT 1, 'C'
UNION ALL
SELECT 2, 'A'
UNION ALL
SELECT 2, 'B'
UNION ALL
SELECT 2, 'C'
UNION ALL
SELECT 2, 'D'
UNION ALL
SELECT 2, 'E'
GO
SELECT *
FROM TestTable
GO

Here is the solution which will build an answer to the above question.

-- Get CSV values
SELECT t.ID, STUFF(
(
SELECT ',' + s.Col
FROM TestTable s
WHERE s.ID = t.ID
FOR XML PATH('')),1,1,'') AS CSV
FROM TestTable AS t
GROUP BY t.ID
GO

I hope this is an easy solution. I am going to point to this blog post in the future for all the similar questions.

Final Clean Up Act
-- Clean up
DROP TABLE TestTable
GO

Here is the question back to you -
Is there any better way to write above script? Please leave a comment and I will write a separate blog post with due credit.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

29 thoughts on “SQL SERVER – Grouping by Multiple Columns to Single Column as A String

  1. This solution is extreme inefficient if you want to get thousands of records. The best solution for the short concatenations (less then 4000 characters) is to create clr aggregation function with StringBuilder class inside clr code. For the longer concatenations you can build clr function that has query as an argument and results in concatenated value of nvarchar(max) type. This time again you can use StringBuilder class inside clr code. The problem is with resource allocation. Every time you perform concatenation you need to allocate new bigger space. In the StringBuilder solution you take the concatenation elements and create single buffer for all elements. There is no waste of time for memory allocations for single concatenations.

    Like

  2. 1. We’re not allowed CLR into prod, so that solution fails.
    2. How about if you have nvarchar() for data types instead of single values? :-). Hint: SPLIT function…
    3. Uh-oh, I actually created a different puzzle altogether…

    Like

  3. This could be alternative solution.

    ;WITH CTE (SRNO,ID,COL)
    AS
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID,COL) AS SRNO,ID,CAST(COL AS VARCHAR(MAX)) FROM TESTTABLE
    )
    ,CTE1(SRNO,ID,COL)
    AS
    (
    SELECT * FROM CTE WHERE SRNO=1
    UNION ALL
    SELECT CTE.SRNO AS SRNO, CTE.ID,CAST (CTE1.COL + ‘,’ + CTE.COL AS VARCHAR(MAX)) FROM CTE INNER JOIN CTE1
    ON CTE.ID=CTE1.ID AND CTE.SRNO=CTE1.SRNO+1
    )
    SELECT ID, MAX(COL) AS CSV FROM CTE1 GROUP BY CTE1.ID

    Like

  4. Here is the another solution using Recursive CTEs ..
    But FOR XML PATH(”) query giving good performance compare to this..

    ;WITH Rnumcte1 AS
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id,Col) AS Rnum,id,cast(Col as varchar(max)) Col
    FROM TestTable
    )
    ,Rnumcte2 AS
    (
    SELECT Rnum,id,Col
    FROM Rnumcte1
    WHERE Rnum = 1

    UNION ALL

    SELECT r1.Rnum,r1.id,r2.Col + ‘,’ + r1.Col
    FROM Rnumcte1 r1
    INNER JOIN Rnumcte2 r2 ON r1.id = r2.id AND r1.Rnum = r2.Rnum + 1
    ) –select * from Rnumcte2
    SELECT r2.id,r2.Col
    FROM (
    SELECT id,MAX(Rnum)maxRnum
    FROM Rnumcte1
    GROUP BY id
    ) r1
    INNER JOIN Rnumcte2 r2 ON r1.id = r2.id AND r1.maxRnum = r2.Rnum
    ORDER BY r2.id

    Like

  5. Select ID,SUBSTRING(
    (Select COALESCE(‘,’+Col,”)
    From TestTable
    Where ID = Master.ID
    For Xml Path(”)),2,500000) As CSV
    From TestTable as Master
    Group By ID

    Like

  6. Create function that will return comma separated value by Id like

    Create function [dbo].[GetCSV](@Id int)
    Returns nvarchar(Max)
    as
    Begin
    Declare @ListStr nvarchar(Max)
    set @ListStr = ”
    select @ListStr = @ListStr + Col + ‘,’ from TestTable where Id = @Id
    Return substring(@ListStr,1,len(@ListStr)-1)
    End

    Then use that function as

    Select Id,dbo.GetCSV(Id) from TestTable group by Id

    Its as easy as simple query writing.

    Like

  7. This works great! However with a small modification it can go a wee bit forward:

    — Get CSV values
    SELECT t.ID, STUFF(
    (SELECT ‘,’ + s.Col
    FROM TestTable s
    WHERE s.ID = t.ID
    FOR XML PATH(”), TYPE).value(‘.’, ‘VARCHAR(MAX)’),1,1,”) AS CSV
    FROM TestTable AS t
    GROUP BY t.ID
    GO

    Adding TYPE ensures that it is available as XML object and doing a type cast to varchar ensure that all the xml-sensitive characters would be converted back from XML. If one of the value was “Me & You” the output would be the same.

    Like

  8. Hi
    I like to GROUP BY a varchar not an int , what changes do I have to do,
    Thanks

    I am Getting This:
    Conversion failed when converting the varchar value ‘H123′ to data type int.
    Thanks

    Like

  9. Nice Article!
    How can I achieve the following. The columns are depend on the values of CSV
    EX:
    ID CSV
    1 A
    1 B
    2 A
    2 B
    2 C
    2 D

    ID CSV1 CSV2 CSV3 CSV4
    1 A B C
    2 A B C D

    Thanks
    Sathish

    Like

  10. Dear Pinal,
    Solution You have provided is not working for SQL COMPACT EDITION 3.5.

    Following is the Error Message:
    “There was an error parsing the query. [ Token line number = 2,Token line offset = 8,Token in error = SELECT ]”

    Please help on this issue.

    Regards,
    Bhavik

    Like

  11. I was received this error

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value ‘,’ to data type int.

    This is my query

    ;WITH TEMP(STDWorkPlanID,STDCCCMapID)
    AS (
    SELECT WP.[STDWorkPlanID],
    (
    SELECT ‘,’+WPM.[STDCCCMapID] ———(This line error ‘,’ )
    FROM [BHAVINI_CCC_STD].[dbo].[STD_CCC_WorkplanMapping] WPM
    WHERE WPM.[STDWorkPlanID]= WP.[STDWorkPlanID]
    FOR XML PATH(”)) AS[STDCCCMapID]
    FROM [BHAVINI_CCC_STD].[dbo].[STDWorkPlan] WP
    WHERE WP.[STDWorkPlanID]= WP.[STDWorkPlanID]
    )
    SELECT STDWorkPlanID,STDCCCMapID FROM TEMP WHERE STDCCCMapID >0;

    Like

  12. i want output if input like this
    ID CSV
    1 A
    1 B
    1 A
    1 B
    2 A
    2 A
    2 B
    2 C
    2 D
    2 D

    ID CSV
    1 A B C
    2 A B C D

    Please reply…
    Bharat

    Like

  13. i have :

    tabel A : id || name ==> A1, A2, A3 || AZ, AX, XX
    tabel B : idd || nameB ==> B1, B2, B3, B4 || BQ, BV, BC, BB
    tabel C : A_ID || B_ID ( foreign key from tabel A and B ) ==> A1, A1, A2, A2, A3 || B2, B1, B1, B3, B4

    I want the result like this :

    name || nameB
    AZ || BV, BQ
    AX || BQ, BC
    XX || BB

    This is my query, but name column won’t come out :

    SELECT A.NAMA, STUFF (
    (select ‘ , ‘ + B.NIT_ID
    FROM JTAB B
    WHERE J.NIK_ID = B.NIK_ID
    FOR XML PATH(”)),1,2,”) AS RES
    FROM Ahli A, TRAINING T, JTAB J
    WHERE J.NIK_ID = A.NIK AND J.NIT_ID = T.NIT
    GROUP BY J.NIK_ID, A.NAMA

    help me please ….

    Like

  14. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  15. Pingback: MySQL – Grouping by Multiple Columns to Single Column as A String | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s