MSSQL中行数据的合并与拆分

1. 概述

在MSSQL中，行数据的合并与拆分非常常见，也非常有用。它们可以用于将一行数据分解为多行或多列，以便更好地进行数据处理。同时，它们也可以用于将多行或多列的数据合并为一行或一列，以便更好地存储和查询数据。

2. 行数据的拆分

2.1. 使用CROSS APPLY拆分为多行

使用CROSS APPLY可以让我们将一行数据拆分成多行，具体的实现方法如下：

-- 原始数据：

-- name    | age | hobbies
-- --------|-----|--------
-- Jason   | 32  | swimming, running, hiking
SELECT 
    q1.name, 
    q1.age, 
    q2.hobby
FROM 
    mytable q1
CROSS APPLY 
    STRING_SPLIT(q1.hobbies, ',') q2;

上面的代码中，我们在SELECT语句中使用了CROSS APPLY和STRING_SPLIT函数。STRING_SPLIT函数是MSSQL 2016及以上版本才支持的，它可以将字符串拆分为多个子字符串。前面的参数是要拆分的字符串，后面的参数是拆分的分隔符。

使用这个方法，我们可以将一行数据拆分成多行，从而更好地进行数据处理。

2.2. 使用UNPIVOT拆分为多列

使用UNPIVOT可以将行数据拆分为多列，具体的实现方法如下：

-- 原始数据：

-- name    | January | February | March
-- --------|---------|----------|-------
-- Jason   | 100     | 200      | 150
SELECT 
    q1.name, 
    q1.month, 
    q1.amount
FROM 
    (SELECT 
         name, 
         January, 
         February, 
         March 
     FROM mytable) p
UNPIVOT 
    (amount FOR month IN (January, February, March)) AS q1;

上面的代码中，我们在SELECT语句中使用了UNPIVOT函数。UNPIVOT函数可以将多列数据合并成一列，并添加一列用于标识原来的列。具体而言，UNPIVOT函数将原来的列作为输入表，将输出表中的每一行表示为一个原始列的值和它的标识列值。

3. 行数据的合并

3.1. 使用FOR XML PATH将多行合并为一行

使用FOR XML PATH可以将多行数据合并为一行，同时也可以添加分隔符。这种方法适用于MSSQL 2005及以上版本。具体的实现方法如下：

-- 原始数据：

-- name  | hobby
-- ------|--------------
-- Jason | swimming
-- Jason | running
-- Jason | hiking
SELECT 
    name, 
    STUFF((SELECT ', ' + hobby 
           FROM mytable q1 
           WHERE q1.name = q2.name 
           FOR XML PATH('')), 1, 2, '') AS hobbies
FROM 
    mytable q2
GROUP BY 
    name;

上面的代码中，我们在SELECT语句中使用了FOR XML PATH函数和STUFF函数。FOR XML PATH函数可以将多行数据合并成一个XML格式的字符串，其中PATH指示XML格式的路径和标签，这里我们使用空字符串。在这个XML格式的字符串中，我们可以使用STUFF函数将逗号和空格分隔符替换为我们想要的分隔符。具体而言，STUFF函数的第一个参数是原始字符串，第二个参数是要用什么字符串替换原始字符串的一部分（在这里我们使用逗号和空格分隔符），第三个参数是开始替换的位置，第四个参数是要替换的字符个数。

3.2. 使用PIVOT将多列合并为一列

使用PIVOT可以将多列数据合并成一列，并添加一列用于标识原来的列。具体的实现方法如下：

-- 原始数据：

-- name  | month   | amount
-- ------|---------|--------
-- Jason | January | 100
-- Jason | February| 200
-- Jason | March   | 150
SELECT 
    name, 
    MAX(CASE WHEN month = 'January' THEN amount ELSE NULL END) AS January, 
    MAX(CASE WHEN month = 'February' THEN amount ELSE NULL END) AS February, 
    MAX(CASE WHEN month = 'March' THEN amount ELSE NULL END) AS March
FROM 
    mytable
GROUP BY 
    name;

上面的代码中，我们在SELECT语句中使用了PIVOT函数。PIVOT函数可以将原来的列作为输入表，将输出表中的每一行表示为一个原始列的值和它的标识列值。具体而言，PIVOT函数在GROUP BY子句中指定要保留的列，并在SELECT子句中使用MAX或MIN等聚合函数将数据展开为一个新的表。

4. 总结

在MSSQL中，我们可以使用各种方法进行行数据的合并和拆分。这些方法可以大大简化我们的数据处理工作，并且可以让我们更好地存储和查询数据。在实际开发中，我们应该根据具体的需求选择合适的方法，从而达到最优的数据处理效果。