SQL Query to get aggregated result in comma separators along with group by column in SQL Server

Question

I need to write a sql query on the table such that the result would have the group by column along with the aggregated column with comma separators.

My table would be in the below format

   |`````````|````````|
   |    ID   |  Value |
   |_________|________|
   |    1    |   a    |
   |_________|________|
   |    1    |   b    |
   |_________|________|
   |    2    |   c    |
   |_________|________|

Expected result should be in the below format

   |`````````|````````|
   |    ID   |  Value |
   |_________|________|
   |    1    |  a,b   |
   |_________|________|
   |    2    |   c    |
   |_________|________|

In MySQL, you would use GROUP_CONCAT. See this question for an example of how to simulate that in SQL server: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — Eric Petroelje, Jun 14 '11 at 14:19
+1 on `cross apply` as @Eric Petroelje states. It is the way to do it. — Mikael Östberg, Jun 14 '11 at 14:23

score 27 · Accepted Answer · edited May 23 '17 at 12:16

27

You want to use FOR XML PATH construct:

select 
    ID, 
    stuff((select ', ' + Value 
           from YourTable t2 where t1.ID = t2.ID 
           for xml path('')),
          1,2,'') [Values]
from YourTable t1
group by ID

The STUFF function is to get rid of the leading ', '.

You can also see another examples here:

edited May 23 '17 at 12:16

Community

1
1

answered Jun 14 '11 at 18:43

Adriano Carneiro

57,693
12
90
123

+1 for your solution. Similarly we can do it using cross apply too. Which would be the best way performance wise? – suryakiran Jun 15 '11 at 03:00
Accepted the answer, but need the best way to perform the operation. – suryakiran Jun 16 '11 at 08:25

score 0 · Answer 2 · answered Jun 14 '11 at 14:49

Just for a balanced view, you can also do this with a CTE but its not as good as the cross apply method I don't think. I've coded this of the hoof so apologies if it doesn't work.

WITH CommaDelimitedCTE (RowNumber,ID,[Value],[Values]) AS
(
  SELECT 1,MT.ID , MIN(MT.Value), CAST(MIN(MT.Value) AS VARCHAR(8000)) 
  FROM  MyTable MT
  GROUP BY MT.ID

  UNION ALL

  SELECT CT.RowNumber + 1, MT.ID, MT.Value, CT.[Values] + ', ' + MT.Value
  FROM  MyTable MT
  INNER JOIN CommaDelimitedCTE CT ON CT.ID = MT.ID
  WHERE MT.[Value] > CT.[Value]
)

Select CommaDelimitedCTE.* from CommaDelimitedCTE 
    INNER JOIN (SELECT MT.ID,MAX(RowNumber) as MaxRowNumber from CommaDelimitedCTE GROUP BY MT.ID) Q on Q.MT.ID = CommaDelimitedCTE.MT.ID
    AND Q.MaxRowNumber = CommaDelimitedCTE.RowNumber

score 0 · Answer 3 · answered Jul 06 '22 at 09:30

In SQL Server 2017 (14.x) and later you can use the STRING_AGG function:

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16

SELECT
  ID,
  STRING_AGG(Value, ',')
FROM TableName
GROUP BY ID

Depending on the data type of Value you might need to convert it:

SELECT
  ID,
  STRING_AGG(CONVERT(NVARCHAR(max), Value), ',')
FROM TableName
GROUP BY ID

SQL Query to get aggregated result in comma separators along with group by column in SQL Server

3 Answers3

Linked

Related