SQL Server:按子句分组以获取逗号分隔的值[duplicate]

Possible Duplicate:
SQL group_concat function in SQL Server

可能重复:SQL Server中的SQL group_concat函数

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            [e-mail]
2     2            [e-mail]
3     1            [e-mail]
4     3            [e-mail]
5     3            [e-mail]

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

我想按ReportId进行分组，但是所有邮件都应该用逗号分隔。所以结果应该是:

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 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.

我正在尝试group by子句，但是如果还有其他事情，那么我也可以实现它。非常感谢您在这方面的时间和帮助。谢谢你！

2 个解决方案

#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

#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