CODEWITHSUNDEEP
sqlsql-serverconcatenationsql-server-group-concat
Stfvns
Stfvns

Reputation: 1041

Multiple rows to one comma-separated value different ID

This question already has an answer here:
Multiple rows to one comma-separated value [duplicate] 1 Answer

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  
 2 | 600
 2 | 700

Now when I execute the query in SQL Server, I want table to be like:

ID | Value
---+-------
 1 | 100,200,300
 2 | 600,700

Upvotes: 3

Views: 5280

Answers (3)

asktonishant
asktonishant

Reputation: 303

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 ║
╚════╩═════════════════════╝

Upvotes: 4

Chanukya
Chanukya

Reputation: 5883

    CREATE TABLE #Table1
        ([ID] int, [Value] int)
    ;

    INSERT INTO #Table1
        ([ID], [Value])
    VALUES
        (1, 100),
        (1, 200),
        (1, 300),
        (2, 600),
        (2, 700)



    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,' ') value
from #table1 t
group by id

output

ID  Value
1    100, 200, 300
2    600, 700

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

Use STUFF method to get your result :

CREATE TABLE #table ( ID INT, Value INT)
INSERT INTO #table ( ID , Value )
SELECT  1 , 100 UNION ALL
SELECT   1 , 200 UNION ALL
SELECT  1 , 300   UNION ALL
SELECT  2 , 600 UNION ALL
SELECT  2 , 700

  SELECT ID , STUFF
 (
    (
     SELECT  ', ' + CAST(Value AS VARCHAR)
     FROM #table Inr WHERE inr.ID = Otr.ID FOR XML PATH('')
     ),1,1,'') 
 FROM #table Otr
 GROUP BY ID

Upvotes: 2

Related Questions