SQL Sever中使用SQL语句实现把重复行数据合并为一行并用逗号分隔

在SQL Server中，有时我们需要将具有相同值的重复行数据合并为一行，并使用逗号作为分隔符。这种操作在数据分析、报告生成或简化显示时非常有用。本文将详细介绍两种方法来实现这一目标，这两种方法都基于SQL Server的内置函数和XML路径功能。 我们创建一个表变量`@T1`，它包含`UserID`、`UserName`和`CityName`三个字段，用于存储示例数据。以下是如何创建和填充这个表变量的SQL语句： ```sql DECLARE @T1 table(UserID int, UserName nvarchar(50), CityName nvarchar(50)); insert into @T1 (UserID, UserName, CityName) values (1, 'a', '上海'), (2, 'b', '北京'), (3, 'c', '上海'), (4, 'd', '北京'), (5, 'e', '上海'); select * from @T1; ``` 现在，我们有了一些重复的`CityName`值，如"上海"和"北京"，我们将它们合并为一行。 ### 方法一：使用`STUFF`和`FOR XML PATH` 这种方法是SQL Server中常用且高效的方式，它利用了`FOR XML PATH`来生成XML字符串，然后使用`STUFF`函数去除首字符的逗号。 ```sql SELECT CityName, STUFF((SELECT ',' + UserName FROM @T1 subTitle WHERE CityName = A.CityName FOR XML PATH('')), 1, 1, '') AS AF FROM @T1 AG GROUP BY CityName; ``` 在这个查询中，`FOR XML PATH('')`将所有相同`CityName`的`UserName`字段组合成一个以逗号分隔的字符串。`STUFF`函数则移除了这个字符串的第一个字符（即多余的逗号）。 ### 方法二：使用`LEFT`、`STUFF`和`FOR XML PATH` 第二种方法稍微复杂一些，它首先生成一个带有所有`UserName`和逗号的字符串列表，然后使用`LEFT`函数去除最后一个逗号。 ```sql SELECT B.CityName, LEFT(UserList, LEN(UserList) - 1) FROM ( SELECT CityName, (SELECT UserName + ',' FROM @T1 WHERE CityName = A.CityName FOR XML PATH('')) AS UserList FROM @T1 A GROUP BY CityName ) B; ``` 在这个查询中，内层子查询生成了每个`CityName`对应的`UserName`列表，外层查询再使用`LEFT`函数去除了末尾的逗号。 两种方法都能达到相同的效果，但第一种方法通常被认为更简洁、效率更高。无论选择哪种方式，都可以根据实际需求和数据库版本来决定最适合的解决方案。 总结来说，SQL Server提供了强大的字符串处理功能，通过`STUFF`、`FOR XML PATH`等函数，我们可以方便地处理重复数据，将其合并为一行，并用逗号分隔。这在处理汇总信息或展示聚合数据时非常有用。在实际工作中，理解并熟练掌握这些技巧，将有助于提高SQL查询的效率和实用性。