107

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

Sanjeev Singh
  • 3,976
  • 3
  • 33
  • 38
  • And your question is? – steoleary Feb 13 '14 at 17:17
  • 1
    Why is ID 1 for all rows? Usually ID is a Primary Key and is usually unique for each row. – Ian R. O'Brien Feb 13 '14 at 17:18
  • SELECT Func1(Value) FROM tbl WHERE ID = 1 output: 100,200,300,400 – Sanjeev Singh Feb 13 '14 at 17:19
  • 1
    The answer you want is here: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – Dylan Brams Feb 13 '14 at 17:20
  • 1
    A "splitter" would do the opposite .. split a single value apart to multiple values. – user2864740 Feb 13 '14 at 17:38
  • You can use string concatenation feature. I can't add answer (since it's locked), so I add answer here: `DECLARE @big_string varchar(max) = ''; SELECT @big_string += x.s + ',' FROM (VALUES ('string1'), ('string2'), ('string3')) AS x(s);`. Now show the result: `SELECT @big_string;`. It's that easy. – JohnyL Aug 02 '18 at 18:36

1 Answers1

245

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 ║
╚════╩═════════════════════╝
M.Ali
  • 67,945
  • 13
  • 101
  • 127