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