sql server stuff（）多行合并成一行

在SQL Server中，STUFF() 函数常与 FOR XML PATH('') 结合使用，以实现将多行数据合并成一行字符串的功能。这种技巧在处理需要将查询结果集中的多行数据拼接成单个字符串（如逗号分隔的列表）时非常有用。

基本用法

假设我们有一个名为 Employees 的表，它包含 ID 和 Name 两个字段，我们想要将 Name 字段中的所有值合并成一个以逗号分隔的字符串。

SELECT STUFF((
    SELECT ', ' + Name
    FROM Employees
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS EmployeeNames

解释

1. 子查询: SELECT ', ' + Name FROM Employees FOR XML PATH(''), TYPE 这部分查询会生成一个XML格式的字符串，其中所有 Name 值都被 , 连接。注意，这里使用了 FOR XML PATH('') 来生成没有XML标签的连续字符串，并使用 TYPE 指令来确保结果是一个XML类型的数据，以便之后可以使用 .value() 方法。

2. STUFF() 函数: 外部的 STUFF() 函数用于移除最前面的 , （逗号加空格）。STUFF() 函数的第一个参数是子查询的结果（即上一步生成的XML类型数据被转换为 NVARCHAR(MAX) 类型后），第二个参数是要开始替换的位置（这里是1，即字符串的开始位置），第三个参数是要替换的字符数（这里是2，即我们要移除的 , 的长度），第四个参数是用来替换的新字符串（这里是空字符串，意味着我们要删除这些字符）。

注意事项

• 这种方法在合并大量数据时可能效率不是非常高，因为它涉及到XML处理和字符串操作。
• 确保在 FOR XML PATH('') 后使用 TYPE 指令，以便可以调用 .value() 方法将XML类型数据转换为字符串类型。
• 如果你的字段值可能包含特殊字符（如 <, >, & 等），这些字符在XML中会有特殊含义，可能需要使用 REPLACE() 函数来预处理这些字段值，以避免XML解析错误。

示例扩展

在SQL Server中，STUFF() 函数常与 FOR XML PATH('') 一起使用，以将多行数据合并成一行输出。这种技巧在处理字符串聚合时非常有用，特别是当你想要将多个记录中的字段值合并成一个单独的、由特定分隔符分隔的字符串时。

下面是一个使用 STUFF() 和 FOR XML PATH('') 来合并多行数据的示例：

假设我们有一个名为 Products 的表，包含以下列：ProductID, ProductName 和 Category。

ProductID	ProductName	Category
1	产品A	类别1
2	产品B	类别1
3	产品C	类别2

如果我们想要为每个 Category 合并 ProductName 到一个以逗号分隔的字符串中，可以编写如下SQL查询：

SELECT 
    Category,
    STUFF((
        SELECT ', ' + ProductName
        FROM Products AS p2
        WHERE p2.Category = p1.Category
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ProductNames
FROM 
    Products AS p1
GROUP BY 
    Category;

这个查询的解释如下：

1. 我们首先选择了 Category 列来确保我们的输出是按类别分组的。
2. 对于每个 Category，我们使用了一个子查询来从 Products 表中检索所有属于该 Category 的 ProductName。这里使用了别名 p2 来引用子查询中的 Products 表，以区分于外层查询中的 p1。
3. 子查询内部，我们将 ProductName 与一个逗号和一个空格连接起来（注意，这个逗号实际上会在每个 ProductName 的前面，包括第一个，这也是为什么我们稍后会使用 STUFF() 函数的原因）。
4. 使用 FOR XML PATH('') 将子查询的结果转换为XML格式，但由于我们实际上只关心生成的文本，所以这里的XML结构并不重要。通过指定空字符串作为 PATH 参数，我们实际上是在生成一个扁平的文本字符串。
5. TYPE 关键字确保了生成的XML被当作 XML 数据类型处理，这对于接下来的 .value('.', 'NVARCHAR(MAX)') 转换是必要的，该转换将XML类型的文本转换回 NVARCHAR(MAX) 类型，以便我们可以使用 STUFF() 函数。
6. 最后，我们使用 STUFF() 函数从生成的字符串中移除第一个逗号加空格（这是子查询中第一个 ProductName 前面的那个）。这里我们指定从字符串的第一个字符开始，删除长度为2的字符串（即逗号加空格）。

这个查询将输出类似于以下的结果：

Category	ProductNames
类别1	产品A, 产品B
类别2	产品C