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

本文介绍了SQL查询以逗号分隔符以及SQL Server中的按列获取聚合结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I need to write a sql query on the table such that the result would have the group by column along with the aggregated column with comma separators.

我的表将格式如下

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

预期结果应采用以下格式

Expected result should be in the below format

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

推荐答案

您要使用 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 函数是为了摆脱前导'，'。

The STUFF function is to get rid of the leading ', '.

您还可以看到另一个此处的示例：

You can also see another examples here:

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

这篇关于SQL查询以逗号分隔符以及SQL Server中的按列获取聚合结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

With