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
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 Hope this help !
its really use full to me, thanks for this blog..