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 中将数据从纵向转换为横向，并且可以根据实际需求进行选择，使用不同的操作符来实现转置查询。