SQL Server:GROUP BY 子句以获取逗号分隔值 [英] SQL Server : GROUP BY clause to get comma-separated values

问题描述

可能的重复:
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?

原始数据

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

我想按 ReportId 分组，但所有电子邮件都应以逗号分隔.所以结果应该是:

I want to group by ReportId, but all the email should be comma separated. So 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?

我正在尝试 group by 子句，但如果还有其他事情，那么我也愿意实施.我非常感谢您的时间和帮助.谢谢.

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.

推荐答案

试试这个:

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 Server:GROUP BY 子句以获取逗号分隔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！