Grouping data in comma separated format

Question

I have a table called SampleData which looks like this:

 col1    col2     
    1       a       
    1       b       
    1       c      
    2       d       
    2       e
    3       f

I need the data in the below format:

col1    col2     
1       a,b,c           
2       d,e       
3       f

Is there a way of doing this using CTE as well?

http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string?rq=1 — KekuSemau, Jul 21 '13 at 11:36
possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Tamim Al Manaseer, Jul 21 '13 at 12:08

score 5 · Accepted Answer · answered Jul 21 '13 at 11:33

5

you can use STUFF if you are using SQL Server 2005 and above.

SELECT
     [col1],
     STUFF(
         (SELECT ',' + [col2]
          FROM Table1
          WHERE [col1] = a.[col1]
          FOR XML PATH ('')) , 1, 1, '')  AS col2
FROM Table1 AS a
GROUP BY [col1]

SQLFiddle Demo

answered Jul 21 '13 at 11:33

John Woo

258,903
69
498
492

thanks,Is there a way of doing this using CTE as in my original requirement this makes very slow – satyajit Jul 21 '13 at 11:36

score 1 · Answer 2 · answered Jul 22 '13 at 05:08

1

I think this is also useful to you.

Comma Seprate Value

answered Jul 22 '13 at 05:08

Hiren gardhariya

1,247
10
29

Grouping data in comma separated format

2 Answers2