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

user_2438446 · Answer

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

https://docs.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 Answers