SQL 查询在 SQL Server 中获取逗号分隔符中的聚合结果以及按列分组

Question

我需要在表上编写一个 sql 查询，以便结果具有按列分组以及带有逗号分隔符的聚合列。

我的表格将采用以下格式

   |`````````|````````|
   |    ID   |  Value |
   |_________|________|
   |    1    |   a    |
   |_________|________|
   |    1    |   b    |
   |_________|________|
   |    2    |   c    |
   |_________|________|

预期结果应采用以下格式

   |`````````|````````|
   |    ID   |  Value |
   |_________|________|
   |    1    |  a,b   |
   |_________|________|
   |    2    |   c    |
   |_________|________|

Answer 1

您想要使用 FOR XML PATH 构造:

select 
    ID, 
    stuff((select ', ' + Value 
           from YourTable t2 where t1.ID = t2.ID 
           for xml path('')),
          1,2,'') [Values]
from YourTable t1
group by ID

STUFF 函数的作用是去掉前面的', '。

您还可以在此处查看另一个示例:

• SQL same unit between two tables needs order numbers in 1 cell
• SQL and Coldfusion left join tables getting duplicate results as a list in one column