[Origin]: https://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value
I want to create a table valued function in SQL Server, which I want to return data in comma separated values.
For example table: tbl
ID | Value
---+-------
1 | 100
1 | 200
1 | 300
1 | 400
Now when I execute the query using the function Func1(value)
SELECT Func1(Value) FROM tbl WHERE ID = 1
Output that I want is: 100,200,300,400
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_Output FROM @Table1 t GROUP BY ID
Result Set
╔════╦═════════════════════╗
║ ID ║ List_Output ║
╠════╬═════════════════════╣
║ 1 ║ 100, 200, 300, 400 ║
╚════╩═════════════════════╝