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 ║╠════╬═════════════════════╣║ 1 ║ 100, 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 ║╠════╬═════════════════════╣║ 1 ║ 100, 200, 300, 400 ║╚════╩═════════════════════╝