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 ║ ╚════╩═════════════════════╝ 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_Output FROM @Table1 GROUP BY ID; Result Set
╔════╦═════════════════════╗ ║ ID ║ List_Output ║ ╠════╬═════════════════════╣ ║ 1 ║ 100, 200, 300, 400 ║ ╚════╩═════════════════════╝ 
