除循环外，SQL中实现分组字符串拼接的更高效方法问询

单列表拼接的常规做法 #

假设有单列表tbl，数据如下：

col
---
A
B
C

要得到拼接字符串A,B,C，可以用变量累加的方式：

DECLARE @s varchar(20)
SELECT @s = @s + col + ',' FROM tbl

这种方式会得到带末尾逗号的A,B,C,，手动去除即可，问题不大。

多列表分组拼接的需求 #

但如果是多列表格，数据如下：

first_name last_name position
---------- --------- --------
Steve      Austin    cook
Steve      Austin    dishwasher
Jaime      Sommers   waitress
Jaime      Sommers   manager

需要按first_name和last_name分组，把同一组的position拼接成逗号分隔的字符串，期望输出：

first_name last_name position
---------- --------- --------
Steve      Austin    cook,dishwasher
Jaime      Sommers   waitress,manager

低效方案的问题 #

如果用生成新表再循环遍历更新的方式，在数据量达数十万时，循环的IO开销极大，效率极低，完全不适用。

高效解决方案 #

方法1：使用STRING_AGG（SQL Server 2017及以上版本） #

这是最简洁高效的方式，直接用内置聚合函数分组拼接：

SELECT 
    first_name,
    last_name,
    STRING_AGG(position, ',') AS position
FROM 
    your_table_name
GROUP BY 
    first_name, last_name;

STRING_AGG会自动处理拼接顺序（默认按数据存储顺序，也可以加WITHIN GROUP (ORDER BY position)指定排序），且不会产生末尾多余逗号。

方法2：FOR XML PATH（兼容SQL Server 2005及以上版本） #

如果用的是旧版本SQL Server，可借助FOR XML PATH实现分组拼接：

SELECT DISTINCT
    t.first_name,
    t.last_name,
    STUFF(
        (SELECT ',' + position 
         FROM your_table_name t2 
         WHERE t2.first_name = t.first_name 
           AND t2.last_name = t.last_name
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 1, ''
    ) AS position
FROM 
    your_table_name t;

• FOR XML PATH('')会把子查询的结果拼接成无标签的XML字符串，比如,cook,dishwasher
• STUFF函数用来去掉开头的逗号，1,1,''表示从第1位开始删除1个字符，替换为空

这两种方法都是基于集合运算，比循环遍历的效率高几个数量级，完全能应对数十万级别的数据量。

内容的提问来源于stack exchange，提问作者Lisa