Search code examples
sqlsql-serverselectaggregate-functionssql-server-group-concat

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

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    |
   |_________|________|
Solution

  • 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: