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
This question is related to
sql-server
sql-server-group-concat
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 ¦
+--------------------------+
Source: Stackoverflow.com