SQLServer行转列实现思路记录

1. 什么是行转列（unpivot）

在SQL Server中，行转列（unpivot）是一种将一列或多列中的行值转换为列的过程。行数据通常是用来展示一条记录的，而列数据通常是当做属性来展示更多元数据信息。如果将数据从行转换为列，可以使数据变得更直观，并且更容易进行比较和分析。下面是一个简单的示例：

CREATE TABLE monthly_sales (
    month INT,
    category VARCHAR(20),
    sales INT
);
INSERT INTO monthly_sales (month, category, sales)
VALUES (1, 'Books', 100),
       (1, 'Food', 200),
       (1, 'Clothes', 150),
       (2, 'Books', 120),
       (2, 'Food', 220),
       (2, 'Clothes', 180);

上面的示例包含了三个列：月份、类别和销售。如果我们想要按照每个月份来查看所有的销售类别及其销售额，我们可以使用行转列的方法：

SELECT month,
       MAX(CASE WHEN category = 'Books' THEN sales ELSE NULL END) AS books_sales,
       MAX(CASE WHEN category = 'Food' THEN sales ELSE NULL END) AS food_sales,
       MAX(CASE WHEN category = 'Clothes' THEN sales ELSE NULL END) AS clothes_sales
FROM monthly_sales
GROUP BY month;

上面的查询将每个月份的销售数据转换成了一行。在这行数据中，每个类别都被转化成了一列，并与其销售额一起显示，这样就可以更轻松地对销售数据进行分析。

2. 行转列的实现方法

2.1 使用UNPIVOT操作符

在SQL Server中，我们可以使用UNPIVOT操作符将行数据转换为列数据。使用UNPIVOT操作符，我们必须指定要转换的列，以及要将转换后的列存储的新列名。下面是一个简单的示例：

SELECT *
FROM (SELECT month,
             category,
             sales
      FROM monthly_sales) AS SourceTable
UNPIVOT (Value FOR CategoryName IN (books_sales, food_sales, clothes_sales)) AS UnpivotTable;

上面的查询将books_sales、food_sales和clothes_sales这三列中的数据转换成了CategoryName这一列，并将每个单元格中的数据存储到新的Value列中，同时保留原有的month、category和sales列。

2.2 使用UNION ALL操作符

在SQL Server中，我们还可以使用UNION ALL操作符实现行转列的过程。用这种方法，我们需要将每个类别的名称写成一个SELECT语句，并将它们使用UNION ALL操作符连接起来，然后再通过使用CASE语句将每个类别的销售额映射到新的列中。下面是一个使用UNION ALL操作符的示例：

SELECT month,
       'Books' AS category,
       SUM(CASE WHEN category = 'Books' THEN sales ELSE 0 END) AS sales
FROM monthly_sales
GROUP BY month
UNION ALL
SELECT month,
       'Food' AS category,
       SUM(CASE WHEN category = 'Food' THEN sales ELSE 0 END) AS sales
FROM monthly_sales
GROUP BY month
UNION ALL
SELECT month,
       'Clothes' AS category,
       SUM(CASE WHEN category = 'Clothes' THEN sales ELSE 0 END) AS sales
FROM monthly_sales
GROUP BY month;

上面的查询将每个月份的销售数据转换成了三行。在这三行数据中，每个类别都被转换成了一列，并与其销售额一起显示，这样我们就可以更轻松地对销售数据进行分析。

3. 行转列的应用场景

行转列的应用场景包括将某个查询结果水平化（即将多行转换为一行），这样的查询结果可以用作报表或者将多个查询结果合并为一个更大的结果集。

下面是一些具体的应用场景：

将多个查询结果合并为一个更大的结果集。例如，将某个表中的每个月份的销售数据转换成一行，以便更容易地进行比较和分析。

将查询结果水平化。例如，将每个学生的分数按照课程水平化，方便比较每个学生在不同课程上的得分情况。

统计某个表格中每个类别的数量或总和。例如，统计某个表格中每个国家的销售总和，这样可以更容易地查看每个国家销售的情况。

4. 总结

行转列（unpivot）是将行数据转换为列数据的过程，在SQL Server中，我们可以使用UNPIVOT操作符或者UNION ALL操作符来实现它。行转列的应用场景包括将某个查询结果水平化、将多个查询结果合并为一个更大的结果集以及统计某个表格中每个类别的数量或总和等。