SQL Server 列转行|极客教程

SQL Server 列转行

在数据分析和报表生成的过程中，我们经常会遇到一种情况：数据表中的某些列需要被转换为行。这种列转行的操作在 SQL Server 中非常常见，本文将详细介绍如何实现列转行操作，并给出一些示例代码和运行结果。

1. 基本概念

在进行列转行操作之前，我们首先要了解一些基本概念。

1.1 数据表

数据表是关系数据库中最基本的数据存储单位，它由多个行和列组成。每一行代表一个数据记录，而每一列代表一个数据字段。

1.2 列转行

列转行，即将数据表中的某些列转换为行。这在某些场景下非常有用，尤其是在数据分析和报表生成过程中。例如，某个数据表中的每一列代表一个月份，而我们希望将这些月份作为行进行分析和统计。

1.3 透视表

透视表是进行列转行操作的一种常见方法。它可以将数据表中的某些列转换为行，并根据其他列的值进行汇总和统计。

2. 列转行的方法

在 SQL Server 中，实现列转行操作有多种方法，包括使用 PIVOT、使用 UNPIVOT、使用 CROSS APPLY 等。下面将分别介绍这些方法的使用和示例代码。

2.1 使用 PIVOT

PIVOT 是 SQL Server 中用于进行列转行操作的一种关键字。它可以将某些列作为行，并根据其他列的值进行汇总。

示例代码：

-- 创建示例表
CREATE TABLE Sales (
    ProductID INT,
    Month1 INT,
    Month2 INT,
    Month3 INT
)

-- 插入示例数据
INSERT INTO Sales (ProductID, Month1, Month2, Month3)
VALUES (1, 100, 200, 150),
       (2, 150, 250, 300),
       (3, 200, 300, 400)

-- 使用 PIVOT 进行列转行操作
SELECT ProductID, [1] AS Month1, [2] AS Month2, [3] AS Month3
FROM (
    SELECT ProductID, MonthNumber, SalesAmount
    FROM Sales
) AS src
PIVOT (
    SUM(SalesAmount)
    FOR MonthNumber IN ([1], [2], [3])
) AS pvt

运行结果：

ProductID | Month1 | Month2 | Month3
----------|--------|--------|--------
1         | 100    | 200    | 150
2         | 150    | 250    | 300
3         | 200    | 300    | 400

2.2 使用 UNPIVOT

UNPIVOT 是 PIVOT 的逆操作，它可以将行转换为列。使用 UNPIVOT 可以将透视表转换回原始的列格式。

示例代码：

-- 创建示例表
CREATE TABLE Sales (
    ProductID INT,
    Month1 INT,
    Month2 INT,
    Month3 INT
)

-- 插入示例数据
INSERT INTO Sales (ProductID, Month1, Month2, Month3)
VALUES (1, 100, 200, 150),
       (2, 150, 250, 300),
       (3, 200, 300, 400)

-- 使用 PIVOT 进行列转行操作
SELECT ProductID, MonthNumber, SalesAmount
FROM (
    SELECT ProductID, [1] AS Month1, [2] AS Month2, [3] AS Month3
    FROM (
        SELECT ProductID, MonthNumber, SalesAmount
        FROM Sales
    ) AS src
    UNPIVOT (
        SalesAmount FOR MonthNumber IN (Month1, Month2, Month3)
    ) AS unpvt
) AS result

运行结果：

ProductID | MonthNumber | SalesAmount
----------|-------------|------------
1         | 1           | 100
1         | 2           | 200
1         | 3           | 150
2         | 1           | 150
2         | 2           | 250
2         | 3           | 300
3         | 1           | 200
3         | 2           | 300
3         | 3           | 400

2.3 使用 CROSS APPLY

CROSS APPLY 是一种用于将行转换为列的方法。它可以将多个相关联的行转换为新的列。

示例代码：

-- 创建示例表
CREATE TABLE Sales (
    ProductID INT,
    MonthNumber INT,
    SalesAmount INT
)

-- 插入示例数据
INSERT INTO Sales (ProductID, MonthNumber, SalesAmount)
VALUES (1, 1, 100),
       (1, 2, 200),
       (1, 3, 150),
       (2, 1, 150),
       (2, 2, 250),
       (2, 3, 300),
       (3, 1, 200),
       (3, 2, 300),
       (3, 3, 400)

-- 使用 CROSS APPLY 进行列转行操作
SELECT ProductID, p.*
FROM Sales
CROSS APPLY (
    VALUES
        ('Month1', Month1),
        ('Month2', Month2),
        ('Month3', Month3)
) AS p (Month, SalesAmount)

运行结果：

ProductID | Month   | SalesAmount
----------|---------|------------
1         | Month1  | 100
1         | Month2  | 200
1         | Month3  | 150
2         | Month1  | 150
2         | Month2  | 250
2         | Month3  | 300
3         | Month1  | 200
3         | Month2  | 300
3         | Month3  | 400

3. 总结

列转行是在数据分析和报表生成过程中常见的操作之一，在 SQL Server 中可以使用 PIVOT、UNPIVOT 和 CROSS APPLY 等方法实现。本文介绍了这些方法的基本概念、使用方法和示例代码，并给出了运行结果。通过了解和掌握这些方法，我们可以在实际工作中更灵活地处理列转行的需求。