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 columns 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)

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

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.

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

SQL Scripts, SQL Server, SQL XML
Previous Post
SQL SERVER – Core Concepts – Elasticity, Scalability and ACID Properties – Exploring NuoDB an Elastically Scalable Database System
Next Post
SQL SERVER – Download Microsoft SQL Server Compact 4.0 SP1

Related Posts

56 Comments. Leave new

  • currentData ExceptedData

    1 1
    1 2
    1 3
    1 4
    2 6
    3 9
    5 14
    6 20
    2 22
    1 23
    1 24
    1 25
    1 26
    1 27

    adding row data that is present in excepted data.

    Reply
  • hi,

    I have total of 5 columns

    id, Col1, Col2, Col3, Col4
    1, a, 1, x, r
    1, b, 2, y, s

    Expected result:

    id, Col1, Col2, Col3, Col4
    1, ‘a,b’, ‘1,2’, ‘x,y’, ‘r,s’

    Any suggested, as i dont want to use multi[le ‘for xml’ and table scans

    Reply
  • Lalit kansara
    August 8, 2019 2:44 pm

    it’s query slow when record will greater than 10000

    Reply
  • The STUFF becomes slower when we are dealing with tables that have million+ records. My source table has 100 million records and the query to group and have the fields comma separated using stuff is running for more than 16 hours – I stopped it as it didn’t seem like a viable option. Any idea on how we achieve this with sql 2016 or below?

    Reply
  • Same here. My table has 100 million + records and it’s never ending. Did you find any other solution? Cannot use string concat as my clients sql is 2016

    Reply

Leave a Reply