SQL Server : GROUP BY clause to get comma-separated values

Question

Possible Duplicate:
SQL group_concat function in SQL Server

I am looking to create a query but somehow I am unable to do so. Can anyone please help me out here?

The original data

ID    ReportId     Email
1     1            a@a.com
2     2            b@b.com
3     1            c@c.com
4     3            d@d.com
5     3            e@e.com

I want to group by ReportId, but all the email should be comma separated. So the result should be:

ReportId     Email
1            a@a.com, c@c.com
2            b@b.com
3            d@d.com, e@e.com

What is the best way to do this?

I am trying the group by clause but if there is any other thing then i am open to implement that also. I really appreciate your time and help on this. Thank you.

and [sql-group-concat-function-in-sql-server](http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server) — gts, Oct 01 '12 at 06:48
Starting with SQL Server 2017 you can use `SELECT ReportId, STRING_AGG(a.Email, ', ') as Email FROM your_table a GROUP BY ReportId` — Paul Nakitare, Oct 28 '22 at 07:19

Joe G Joseph · Accepted Answer · 2012-10-01T06:46:22.023

218

try this:

SELECT ReportId, Email = 
    STUFF((SELECT ', ' + Email
           FROM your_table b 
           WHERE b.ReportId = a.ReportId 
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY ReportId

SQL fiddle demo

edited Oct 01 '12 at 06:46

answered Oct 01 '12 at 06:40

Joe G Joseph

23,518
5
56
58

14

@joe is there any other efficient way of doing this? I have a table with 30,000 records and it's taking 20 seconds... – yogi Jan 27 '15 at 11:30
1

If you don't want the space with the comma, you need to do 1,1,'' instead of 1,2,'' – Evan M Mar 09 '17 at 15:48
1

could you add some text about how this works? – Prashanth Subramanian May 23 '20 at 07:48
8

I know its very old post, but if you are using SQL server 2017, you can use STRING_AGG() function. – Adil Ansari Oct 15 '20 at 08:43
1

Fiddle with STRING_AGG() [link](http://sqlfiddle.com/#!18/27b86/751) – Paul Nakitare Oct 28 '22 at 07:21
@PaulNakitare You should add this as answer. Simplest solution. Thanks – Krish Jan 12 '23 at 06:42

score 26 · Answer 2 · edited Mar 22 '18 at 15:08

26

SELECT  [ReportId], 
        SUBSTRING(d.EmailList,1, LEN(d.EmailList) - 1) EmailList
FROM
        (
            SELECT DISTINCT [ReportId]
            FROM Table1
        ) a
        CROSS APPLY
        (
            SELECT [Email] + ', ' 
            FROM Table1 AS B 
            WHERE A.[ReportId] = B.[ReportId]
            FOR XML PATH('')
        ) D (EmailList)

SQLFiddle Demo

edited Mar 22 '18 at 15:08

GôTô

7,974
3
32
43

answered Oct 01 '12 at 06:40

John Woo

258,903
69
498
492

2

This worked for a complex join whereas the other would not. Underrated. – Magic Octopus Urn Oct 10 '19 at 15:43

SQL Server : GROUP BY clause to get comma-separated values

2 Answers2

SQL fiddle demo

SQLFiddle Demo

Linked

Related