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.

2 Answers

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

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