SQL Server: GROUP BY clause to get comma separated values

Question

Possible duplicate:
SQL group_concat function in SQL Server

I want to create a query, but somehow I cannot do it. Can anyone help me here?

Initial data

ID ReportId Email 1 1 [e-mail] 2 2 [e-mail] 3 1 [e-mail] 4 3 [e-mail] 5 3 [e-mail] 

I want to group ReportId , but all emails should be separated by a comma. Thus, the result should be:

 ReportId Email 1 [e-mail], [e-mail] 2 [e-mail] 3 [e-mail], [e-mail] 

What is the best way to do this?

I am trying a group by clause, but if there is any other thing, then I am also ready to implement this. I really appreciate your time and help with this. Thank.

Answer 1

try the following:

 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 script demonstration

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