如何从SQL Server将数据纵向转横向

1. 概述

在 SQL Server 中，转置或翻转行和列的需求是很常见的，这种需求在分析数据和生成报表时非常有用。

这篇文章将介绍如何将 SQL Server 中的数据从纵向转换为横向，这也被称为 SQL Server 中的“转置查询”。

2. 转置查询的基本概念

转置查询是将一个数据集中的行转换为一列或多列，并将多列数据集变成一行或多行。

在SQL Server中，我们可以使用 PIVOT 和 UNPIVOT 这两个操作符来实现转置查询。

2.1 PIVOT 操作符

PIVOT 操作符是将行值转换为列值的一种方式，用于创建横向汇总数据集。使用 PIVOT 时，你必须指定:

要聚合的值 - 聚合函数的要求，计算值的类型

要转换的列 - 行值的列

要显示在结果集中的行 - 转换的列的每个 不同 值对应的每个列

2.2 UNPIVOT 操作符

UNPIVOT 操作符是将列值转换为行值的一种方式，用于创建纵向汇总数据集。使用 UNPIVOT 时，你必须指定:

要转换的列 - 列名称的列表

要排除的列 - 不需要转换成行的其他列

3. 一个基本例子

假设我们有一个员工工资表，其中每个员工在不同月份的工资如下所示:

CREATE TABLE Employee (
    EmpID VARCHAR(10),
    SalaryMonth VARCHAR(20),
    Salary INT
);
INSERT INTO Employee VALUES('E001', 'Jan', 8000);
INSERT INTO Employee VALUES('E001', 'Feb', 9000);
INSERT INTO Employee VALUES('E001', 'Mar', 10000);
INSERT INTO Employee VALUES('E002', 'Jan', 7000);
INSERT INTO Employee VALUES('E002', 'Feb', 8000);
INSERT INTO Employee VALUES('E002', 'Mar', 9000);

我们希望将每个员工的工资在一行中进行显示，而不是像上面那样分成多行。下面就是一个使用 PIVOT 操作符来解决这个问题的例子:

SELECT EmpID, Jan, Feb, Mar
FROM (
    SELECT EmpID, SalaryMonth, Salary
    FROM Employee
) AS SourceTable
PIVOT (
    MAX(Salary)
    FOR SalaryMonth IN (Jan, Feb, Mar)
) AS PivotTable;

这个查询将返回以下结果:

EmpID | Jan  | Feb  | Mar
------+------|------|-----
E001  | 8000 | 9000 | 10000
E002  | 7000 | 8000 | 9000

上面的 PIVOT 查询中有两个部分:

子查询：这个子查询从 Employee 表中选择出 EmpID、SalaryMonth 和 Salary 这3个字段，作为 PIVOT 操作符的输入。

PIVOT 操作符：这个操作符将 SalaryMonth 列中的唯一值转换为列，并使用 MAX 函数聚合 Salary 值。

4. 解析一个复杂例子

接下来，我们将解析一个稍微复杂的例子来详细说明如何使用 PIVOT 和 UNPIVOT 操作符。

假设我们有一个销售数据表，其中包含每个员工在每个季度的销售额。如下所示:

CREATE TABLE Sales (
    EmpID VARCHAR(10),
    Quarter INT,
    Country VARCHAR(20),
    Sales INT
);
INSERT INTO Sales VALUES('E001', 1, 'USA', 10000);
INSERT INTO Sales VALUES('E001', 2, 'USA', 15000);
INSERT INTO Sales VALUES('E001', 3, 'USA', 12000);
INSERT INTO Sales VALUES('E001', 4, 'USA', 18000);
INSERT INTO Sales VALUES('E002', 1, 'UK', 5000);
INSERT INTO Sales VALUES('E002', 2, 'UK', 8000);
INSERT INTO Sales VALUES('E002', 3, 'UK', 7000);
INSERT INTO Sales VALUES('E002', 4, 'UK', 7000);

我们希望在一张表中列出每个员工的国家和销售数据，以及这些销售数据所在的季度。查询输出应该如下所示：

EmpID | Country | Q1   | Q2   | Q3   | Q4
------+---------|------|------|------|-----
E001  | USA     | 10000| 15000| 12000| 18000
E002  | UK      | 5000 | 8000 | 7000 | 7000

4.1 将每个季度转换成列

首先，我们需要使用 PIVOT 操作符将每个季度转换成列。

SELECT EmpID, Country, [1] AS Q1, [2] AS Q2, [3] AS Q3, [4] AS Q4
FROM (
    SELECT EmpID, Quarter, Country, Sales
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Quarter IN ([1], [2], [3], [4])
) AS PivotTable;

这个查询执行以下步骤：

使用子查询从 Sales 表中选择 EmpID、Quarter、Country 和 Sales 字段，并用它作为 PIVOT 操作符的输入。

使用 PIVOT 操作符将四个季度中的每一个季度值(1, 2, 3, 4)转换成一列。

使用 SUM(Sales) 聚合函数对每个季度的销售额总计进行求和。

查询执行后，会得到以下结果：

EmpID | Country | Q1   | Q2   | Q3   | Q4
------+---------|------|------|------|-----
E001  | USA     | 10000| 15000| 12000| 18000
E002  | UK      | 5000 | 8000 | 7000 | 7000

4.2 再次转置表格，将季度转换回行

如果我们希望将季度转换回行，可以使用 UNPIVOT 操作符。

SELECT EmpID, Country, Quarter, Sales
FROM (
    SELECT EmpID, Country, [1], [2], [3], [4]
    FROM (
        SELECT EmpID, Quarter, Country, Sales
        FROM Sales
    ) AS SrcTable
    PIVOT (
        SUM(Sales)
        FOR Quarter IN ([1], [2], [3], [4])
    ) AS PivotTable
) AS PivotTable2
UNPIVOT (
    Sales FOR Quarter IN ([1], [2], [3], [4])
) AS UnpivotTable;

这个查询包含以下步骤：

以相同的方式使用 PIVOT 重新创建结果表格。

使用 UNPIVOT 操作符将列“Q1”、“Q2”、“Q3”和“Q4”转换回行。

查询执行后，会得到以下结果：

EmpID | Country | Quarter | Sales
------+---------|---------|-------
E001  | USA     | 1       | 10000
E001  | USA     | 2       | 15000
E001  | USA     | 3       | 12000
E001  | USA     | 4       | 18000
E002  | UK      | 1       | 5000
E002  | UK      | 2       | 8000
E002  | UK      | 3       | 7000
E002  | UK      | 4       | 7000

5. 总结

在 SQL Server 中，转置查询是将行值转换为列值的一种方式。使用 PIVOT 操作符可以将列中的唯一值转换为列，并使用聚合函数对数据进行聚合。而使用 UNPIVOT 操作符则可以实现将列值转换为行值的功能。

通过对本文例子的分析，我们可以更好地理解如何在 SQL Server 中将数据从纵向转换为横向，并且可以根据实际需求进行选择，使用不同的操作符来实现转置查询。