SQL Server行转列的方法解析-猿码集

1.什么是行转列？

在数据库中，行表示表中的每个记录，而列表示表中的每个字段。行转列意味着把一些列作为行，把一些行作为列，这有助于向表中添加新的维度，并简化数据报表。

1.1 示例数据表

假设我们有如下的数据表：


CREATE TABLE sales (
    ProductName VARCHAR(50),
    SalesYear INT,
    SalesMonth INT,
    SalesAmount DECIMAL(18,2)
);
INSERT INTO sales (ProductName, SalesYear, SalesMonth, SalesAmount) VALUES
('Product A', 2018, 1, 5000),
('Product A', 2018, 2, 6000),
('Product A', 2018, 3, 7000),
('Product A', 2018, 4, 8000),
('Product A', 2018, 5, 9000),
('Product B', 2018, 1, 4500),
('Product B', 2018, 2, 5500),
('Product B', 2018, 3, 6500),
('Product B', 2018, 4, 7500),
('Product B', 2018, 5, 8500),
('Product C', 2018, 1, 4000),
('Product C', 2018, 2, 5000),
('Product C', 2018, 3, 6000),
('Product C', 2018, 4, 7000),
('Product C', 2018, 5, 8000);

上述数据表是一个简单的销售记录表，它包含每个产品在每个月份的销售额。

2.如何进行行转列？

SQL Server中，可以使用PIVOT关键字来进行行列转换。具体操作如下：

2.1 PIVOT在行转列中的使用

PIVOT关键字可以帮助我们把行转成列，同时还可以对数据进行汇总。下面是使用PIVOT关键字把sales表的SalesAmount字段行转列：


SELECT * 
FROM
(
    SELECT ProductName, SalesYear, SalesMonth, SalesAmount
    FROM sales
) AS s
PIVOT
(
    SUM(SalesAmount)
    FOR SalesMonth IN ([1], [2], [3], [4], [5])
) AS p

通过PIVOT关键字实现了把sales表中SalesAmount字段行转列的效果，新的查询结果如下：


ProductName | SalesYear | 1        | 2        | 3        | 4        | 5
Product A   | 2018      | 5000.00  | 6000.00  | 7000.00  | 8000.00  | 9000.00
Product B   | 2018      | 4500.00  | 5500.00  | 6500.00  | 7500.00  | 8500.00
Product C   | 2018      | 4000.00  | 5000.00  | 6000.00  | 7000.00  | 8000.00

2.2 UNPIVOT在列转行中的使用

在SQL Server中，可以使用UNPIVOT关键字来实现列转行。下面四个步骤介绍如何使用UNPIVOT把新的表格转换为旧表格：

2.2.1 建立新表格


CREATE TABLE sales_new (
    ProductName VARCHAR(50),
    SalesYear INT,
    SalesAmount_1 DECIMAL(18,2),
    SalesAmount_2 DECIMAL(18,2),
    SalesAmount_3 DECIMAL(18,2),
    SalesAmount_4 DECIMAL(18,2),
    SalesAmount_5 DECIMAL(18,2)
)

2.2.2 分组并汇总数据


INSERT INTO sales_new (ProductName, SalesYear, SalesAmount_1, SalesAmount_2, SalesAmount_3, SalesAmount_4, SalesAmount_5)
SELECT ProductName, SalesYear, 
       SUM(CASE WHEN SalesMonth = 1 THEN SalesAmount ELSE 0 END) AS SalesAmount_1,
       SUM(CASE WHEN SalesMonth = 2 THEN SalesAmount ELSE 0 END) AS SalesAmount_2,
       SUM(CASE WHEN SalesMonth = 3 THEN SalesAmount ELSE 0 END) AS SalesAmount_3,
       SUM(CASE WHEN SalesMonth = 4 THEN SalesAmount ELSE 0 END) AS SalesAmount_4,
       SUM(CASE WHEN SalesMonth = 5 THEN SalesAmount ELSE 0 END) AS SalesAmount_5
FROM sales
GROUP BY ProductName, SalesYear

2.2.3 UNPIVOT操作

UNPIVOT关键字用于把列转化成行，下面的代码把 sales_new 表中的 SalesAmount_1 到 SalesAmount_5 列转变成 SalesMonth 和 SalesAmount 两个列：


SELECT ProductName, SalesYear, SalesMonth, SalesAmount
FROM sales_new
UNPIVOT
(
    SalesAmount FOR SalesMonth IN (SalesAmount_1, SalesAmount_2, SalesAmount_3, SalesAmount_4, SalesAmount_5)
) AS up

新的查询结果如下：


ProductName | SalesYear | SalesMonth | SalesAmount
Product A   | 2018      | 1         | 5000.00
Product A   | 2018      | 2         | 6000.00
Product A   | 2018      | 3         | 7000.00
Product A   | 2018      | 4         | 8000.00
Product A   | 2018      | 5         | 9000.00
Product B   | 2018      | 1         | 4500.00
Product B   | 2018      | 2         | 5500.00
Product B   | 2018      | 3         | 6500.00
Product B   | 2018      | 4         | 7500.00
Product B   | 2018      | 5         | 8500.00
Product C   | 2018      | 1         | 4000.00
Product C   | 2018      | 2         | 5000.00
Product C   | 2018      | 3         | 6000.00
Product C   | 2018      | 4         | 7000.00
Product C   | 2018      | 5         | 8000.00

2.2.4 删除新表格

完成列转行之后，我们可以根据自己的需求选择保留或删除新表格：

DROP TABLE IF EXISTS sales_new

3.行转列和列转行的优缺点

3.1 行转列的优点

提供更多的展示需求

方便进行数据汇总、分类、排序

通过对行列转换可以有效地减少数据宽表数量

3.2 行转列的缺点

占用物理存储空间较多

查询效率较低，数据量较大时可能出现性能问题

在列名称动态化的情况下需要使用动态SQL，较难维护

3.3 列转行的优点

提升查询性能，减少大表单次查询的数据量

简化数据模型

易于维护

3.4 列转行的缺点

需要占用较多的存储空间

会增加表的写入复杂性

不适合需要频繁更新的数据

4.总结

本文主要介绍了行转列和列转行操作在SQL Server中的具体实现方法，并从各个角度探讨了其优缺点。行转列和列转行虽然都可以提供更好的数据展示效果，但要根据实际需求和业务场景选择合适的转换方式。希望本文能为各位读者提供参考。

SQL Server行转列的方法解析