适用版本：SQL Server 2016 及以下（2017+ 推荐使用 STRING_AGG）

问题背景

在实际开发中，常常需要将多行数据合并为一个字符串。例如：

原始数据（员工表）：

目标结果：

SQL Server 低版本没有内置的字符串聚合函数，需要组合使用以下几个技巧来实现。

完整写法（拼接字段没有去重）

SELECT
    部门,
    STUFF(
        (
            SELECT TOP 100 PERCENT ', ' + 员工
            FROM 员工表 AS b
            WHERE b.部门 = a.部门
            ORDER BY 员工
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
        , 1, 2, ''
    ) AS 员工列表
FROM 员工表 AS a
GROUP BY 部门;

逐步拆解

Step 1：FOR XML PATH('') —— 核心：把多行拼成一个字符串

FOR XML PATH 本来是用于生成 XML 的子句，但当路径名设为空字符串 '' 时，SQL Server 会直接把多行的列值无间隔地拼接成一个字符串，不添加任何 XML 标签。

-- 子查询（以技术部为例）
SELECT ', ' + 员工
FROM 员工表
WHERE 部门 = '技术部'
FOR XML PATH('')

执行过程：

行1：', 张三'
行2：', 李四'
行3：', 王五'
↓ FOR XML PATH('') 拼接后
结果：', 张三, 李四, 王五'   ← 开头有多余的 ", "

⚠️ 为什么不直接用字符串相加？ SQL 的聚合函数（SUM、MAX 等）不支持字符串累加拼接，FOR XML PATH 是绕过这一限制的经典技巧。

Step 2：TYPE 和 .value('.', 'NVARCHAR(MAX)') —— 防止特殊字符被转义

FOR XML PATH 默认返回的是 XML 类型，会对特殊字符自动做 XML 转义：

如果数据里含有这些字符（如 研发 & 测试），不处理就会变成乱码（研发 & 测试）。

解决方案：加上 TYPE，再用 .value() 提取纯文本

-- 加 TYPE 后返回真正的 XML 对象
-- .value('.', 'NVARCHAR(MAX)') 将 XML 对象还原为普通字符串
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')

, TYPE 是 FOR XML PATH 子句的一个修饰选项，它改变了 FOR XML 的返回值类型：不加时结果会被隐式转为字符串（特殊字符同时被转义），加上后结果保持为 XML 对象，后续再用 .value() 提取，特殊字符才能被正确还原。

.value() 是 SQL Server XML 数据类型的一个方法，用于从 XML 对象中提取出一个标量值（普通的字符串、数字等）。

语法：

xml对象.value(XPath表达式, '目标数据类型')

在多行合并场景中的用法：

.value('.', 'NVARCHAR(MAX)')

两个参数的含义：

• 第一个参数 '.'：XPath 表达式，. 表示"当前节点"，也就是取整个 XML 对象的文本内容，不做任何筛选
• 第二个参数 'NVARCHAR(MAX)'：目标数据类型，告诉 SQL Server 把提取出来的内容转换成什么类型，这里转为普通字符串

所以 .value('.', 'NVARCHAR(MAX)') 整体的意思就是：把整个 XML 对象的文本内容，原样提取出来转成一个普通字符串。 配合前面的 TYPE 使用，就完成了"XML 对象 → 纯文本字符串"的最后一步还原。

两步的分工：

FOR XML PATH(''), TYPE   → 返回 XML 类型对象（字符不被隐式转义为字符串）
.value('.', 'NVARCHAR(MAX)')  → 从 XML 对象中提取纯文本，特殊字符正确还原

✅ 即使没有特殊字符，养成写 TYPE).value(...) 的习惯，可以避免潜在 Bug。

Step 3：STUFF —— 去掉开头多余的分隔符

经过 Step 1、2 处理后，结果是：

', 张三, 李四, 王五'

开头有一个多余的 , ，需要删掉。STUFF 函数专门用于在字符串中删除或替换一段内容。

语法：

STUFF(原字符串, 起始位置, 删除长度, 替换字符串)

使用方式：

STUFF(', 张三, 李四, 王五', 1, 2, '')
--    从第1个字符开始，删除2个字符（即 ", "），替换为空字符串
-- 结果：'张三, 李四, 王五'  ✓

Step 4：SELECT TOP 100 PERCENT ... ORDER BY —— 让子查询支持排序

在 SQL Server 中，子查询内不允许直接使用 ORDER BY，除非配合 TOP、OFFSET 等子句。

TOP 100 PERCENT 的含义是 " 取全部行的 100%"，相当于不过滤任何行，但它的存在让解析器允许 ORDER BY 语法通过。

SELECT TOP 100 PERCENT ', ' + 员工
FROM 员工表 AS b
WHERE b.部门 = a.部门
ORDER BY 员工   -- 控制拼接顺序
FOR XML PATH(''), TYPE

⚠️ 注意： SQL Server 优化器在某些情况下可能会忽略此排序（不保证结果顺序）。 如需严格保证顺序，可以用以下替代写法：

SELECT ', ' + 员工
FROM 员工表 AS b
WHERE b.部门 = a.部门
ORDER BY 员工
OFFSET 0 ROWS   -- 替代 TOP 100 PERCENT，更可靠
FOR XML PATH(''), TYPE

OFFSET 0 ROWS 是分页查询语法 OFFSET...FETCH 的一部分，原本的用途是"跳过 0 行，从第 1 行开始取数据"，也就是不跳过任何行，取全部结果。

它和 TOP 100 PERCENT 的目的一样，都是为了让子查询中的 ORDER BY 语法合法——SQL Server 规定，子查询里写 ORDER BY 必须配合 TOP 或 OFFSET 才不会报错。

为什么说它更可靠？

TOP 100 PERCENT 是一个"假动作"，SQL Server 优化器很清楚"取 100% 就是取全部"，所以会直接把 ORDER BY 优化掉忽略掉，导致排序实际上不生效。而 OFFSET 0 ROWS 是真正的分页指令，优化器不会轻易把它忽略，排序结果相对更能得到保证。

完整执行流程图

子查询（每个部门）
    │
    ├─ SELECT ', ' + 员工  →  多行：', 张三' / ', 李四' / ', 王五'
    │
    ├─ FOR XML PATH('')    →  拼接成一行：', 张三, 李四, 王五'
    │
    ├─ TYPE                →  保持为 XML 对象（防转义）
    │
    └─ .value(...)         →  还原为普通字符串：', 张三, 李四, 王五'

外层 STUFF(...)
    └─ 删除开头 2 个字符   →  最终结果：'张三, 李四, 王五'  ✓

各组件总结

自定义分隔符

只需修改子查询中的前缀字符串，并调整 STUFF 的删除长度即可：

-- 用顿号 "、" 分隔（1个字符）
STUFF((
    SELECT TOP 100 PERCENT '、' + 员工
    FROM 员工表 AS b WHERE b.部门 = a.部门
    ORDER BY 员工
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')  -- 删除长度改为 1
-- 用 " | " 分隔（3个字符）
STUFF((
    SELECT TOP 100 PERCENT ' | ' + 员工
    FROM 员工表 AS b WHERE b.部门 = a.部门
    ORDER BY 员工
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 3, '')  -- 删除长度改为 3

规律： STUFF 的删除长度 = 分隔符的字符数。

拼接字段有重复值时：用 GROUP BY 去重

什么情况下会出现重复？

员工表结构简单时通常没有重复，但实际业务中表往往有更多字段，比如：

张三参与了两个项目，所以有两行记录。此时直接拼接员工姓名，张三就会重复出现：

张三, 张三, 李四   ← 不是想要的结果

解决方案：内层子查询用 GROUP BY 去重

把原来的单层子查询改为两层，内层先去重再排序，外层再拼接：

SELECT
    部门,
    STUFF(
        (
            SELECT ', ' + b.员工
            FROM (
                SELECT TOP 100 PERCENT 员工
                FROM 员工表
                WHERE 部门 = a.部门
                GROUP BY 员工      -- 对员工去重，每个姓名只保留一行
                ORDER BY 员工      -- 控制排序
            ) AS b
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
        , 1, 2, ''
    ) AS 员工列表
FROM 员工表 AS a
GROUP BY 部门;

两层子查询的分工：

内层子查询
  → WHERE  过滤出当前部门的员工
  → GROUP BY 员工  去重，每个员工只保留一行
  → ORDER BY 员工  控制排序

外层子查询
  → SELECT ', ' + b.员工  拼接分隔符
  → FOR XML PATH(''), TYPE  合并成一行

STUFF
  → 去掉开头多余的 ', '

去重前后对比：

不加 GROUP BY  →  '张三, 张三, 李四'   ✗
加了 GROUP BY  →  '张三, 李四'         ✓

📌 为什么用 GROUP BY 而不是 DISTINCT？ 因为子查询同时需要 ORDER BY，而 SELECT DISTINCT ... ORDER BY 在某些写法下会有语法限制，GROUP BY 既能去重又能配合 ORDER BY，是更稳妥的写法。

现代替代方案（SQL Server 2017+）

SQL Server 2017 引入了 STRING_AGG，直接替代上述写法，更简洁直观：

SELECT
    部门,
    STRING_AGG(员工, ', ') WITHIN GROUP (ORDER BY 员工) AS 员工列表
FROM 员工表
GROUP BY 部门;

去重场景下的 STRING_AGG 写法

STRING_AGG 本身不支持 DISTINCT，如果数据有重复，需要先用子查询去重再聚合：

SELECT
    部门,
    STRING_AGG(员工, ', ') WITHIN GROUP (ORDER BY 员工) AS 员工列表
FROM (
    SELECT DISTINCT 部门, 员工   -- 先去重
    FROM 员工表
) AS t
GROUP BY 部门;

新旧方案对比

📌 最佳实践建议：

• 项目数据库版本 ≥ 2017：优先使用 STRING_AGG
• 需要兼容低版本：使用 STUFF + FOR XML PATH('') 组合
• 数据中可能含有 &、<、> 等字符时：务必加上 TYPE).value(...)