Multiple rows to one comma-separated value [duplicate]

[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

shareimprove this question

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 
╚════╩═════════════════════╝
shareimprove this answer
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s