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

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            [email protected]
2     2            [email protected]
3     1            [email protected]
4     3            [email protected]
5     3            [email protected]

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

ReportId     Email
1            [email protected], [email protected]
2            [email protected]
3            [email protected], [email protected]

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.

Answer 1

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

Answer 2

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