Multiple rows to one comma-separated value in Sql Server [duplicate] Multiple rows to one comma-separated value in Sql Server [duplicate] sql-server sql-server

Multiple rows to one comma-separated value in Sql Server [duplicate]


Test Data

DECLARE @Table1 TABLE(ID INT, Value INT)INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)

Query

SELECT  ID       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]         FROM @Table1          WHERE ID = t.ID         FOR XML PATH(''), TYPE)        .value('.','NVARCHAR(MAX)'),1,2,' ') List_OutputFROM @Table1 tGROUP BY ID

Result Set

╔════╦═════════════════════╗║ ID ║     List_Output     ║╠════╬═════════════════════╣║  1100, 200, 300, 400 ║╚════╩═════════════════════╝

SQL Server 2017 and Later Versions

If you are working on SQL Server 2017 or later versions, you can use built-in SQL Server Function STRING_AGG to create the comma delimited list:

DECLARE @Table1 TABLE(ID INT, Value INT);INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400);SELECT ID , STRING_AGG([Value], ', ') AS List_OutputFROM @Table1GROUP BY ID;

Result Set

╔════╦═════════════════════╗║ ID ║     List_Output     ║╠════╬═════════════════════╣║  1100, 200, 300, 400 ║╚════╩═════════════════════╝