MSSQL 多行数据合并的实现

什么是多行数据合并

多行数据合并是指将多行数据中的某些列的值合并成一行，以便更好地展示数据或进行数据分析。

举个例子

假设有如下的订单数据表：

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10,2)
);
INSERT INTO orders VALUES 
(1, 1001, '2021-01-01', 100.00),
(2, 1001, '2021-01-02', 200.00),
(3, 1002, '2021-01-03', 150.00),
(4, 1002, '2021-01-04', 250.00);

我们想要以每个顾客为单位展示订单数据，合并每个顾客的订单总金额，可以使用多行数据合并的方法。

使用STUFF函数多行数据合并

SQL Server 中可以使用STUFF函数实现多行数据合并。

语法

STUFF ( character_expression , start , length , replacement_characters )

参数说明：

character_expression：要替换的字符表达式，可以是列名或字符串常量。

start：替换开始的位置，从1开始计数。

length：要替换的字符数。

replacement_characters：替换的字符串。

使用示例

使用STUFF函数将订单数据按顾客合并：

SELECT customer_id, 
    STUFF((SELECT ',' + CAST(total_amount AS VARCHAR(10)) 
           FROM orders o2 
           WHERE o1.customer_id = o2.customer_id 
           FOR XML PATH('')), 1, 1, '') AS total_amount
FROM orders o1
GROUP BY customer_id;

输出结果：

customer_id | total_amount
------------+-------------
1001        | 100.00,200.00
1002        | 150.00,250.00

以上SQL语句的含义是，在orders表格中对于每个订单，先找到与这个订单相同的customer_id，然后将这个customer_id对应的所有订单的total_amount拼接成一个字符串，中间以逗号隔开，最后作为新的一列total_amount添加到结果集中。注意，这里使用了子查询和XML PATH方式，这是STUFF函数使用的常见方式。

使用FOR XML PATH方法多行数据合并

除了STUFF函数，SQL Server 还提供了使用FOR XML PATH方法实现多行数据合并的方法。

语法

SELECT column_name_1, column_name_2, ..., column_name_n, 
       (SELECT column_or_expression FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') AS alias
FROM table_name;

在主查询的SELECT语句中，将要合并的列放在SELECT后面，并将这些列用逗号分隔。然后使用子查询方式将这些列拼接为一个字符串，使用FOR XML PATH('')方法表示拼接为一个XML片段，并指定TYPE属性为XML类型。最后使用.value方法将这个XML片段转为VARCHAR类型，并取个别名。

使用示例

使用FOR XML PATH方法将订单数据按顾客合并：

SELECT customer_id, 
       (SELECT CAST(total_amount AS VARCHAR(10)) + ',' 
        FROM orders o2 
        WHERE o1.customer_id = o2.customer_id 
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') AS total_amount
FROM orders o1
GROUP BY customer_id;

输出结果：

customer_id | total_amount
------------+-------------
1001        | 100.00,200.00
1002        | 150.00,250.00

以上SQL语句的含义与STUFF函数的示例相同，使用了子查询和XML PATH方式将多行数据拼接为一个字符串。

结语

本文介绍了两种在SQL Server中实现多行数据合并的方法，即STUFF函数和FOR XML PATH方法。这两种方法通过将多行数据按某个列合并，展现了数据更全面的视野和信息，为数据分析提供了支持。