FROM - 使用 PIVOT 和 UNPIVOT
适用于：
SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 中的 SQL 终结点 Microsoft Fabric 中的仓库 Microsoft Fabric SQL 数据库
可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。 PIVOT 通过将表达式中的一个列的唯一值转换为输出中的多列，来轮替表值表达式。 PIVOT 还会运行聚合，在这些聚合中，它们需要针对最终输出中所需的任何剩余列值。 UNPIVOT 通过将表值表达式的列旋转为列值来执行相反的操作 PIVOT。
与在复杂语句系列SELECT...CASE中指定的语法相比，该PIVOT语法更容易且更易于阅读。 有关语法 PIVOT的完整说明，请参阅 FROM 子句。
注意
在单个 T-SQL 语句中重复使用 PIVOT/UNPIVOT 可能会对查询性能产生负面影响。
本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。
语法
本部分总结了如何使用 PIVOT and UNPIVOT 运算符。
运算符的 PIVOT 语法。
SELECT [ <non-pivoted column> [ AS <column name> ] , ]
...
[ <first pivoted column> [ AS <column name> ] ,
[ <second pivoted column> [ AS <column name> ] , ]
...
[ <last pivoted column> [ AS <column name> ] ] ]
FROM
( <SELECT query that produces the data> )
AS <alias for the source query>
PIVOT
(
<aggregation function> ( <column being aggregated> )
FOR <column that contains the values that become column headers>
IN ( <first pivoted column>
, <second pivoted column>
, ... <last pivoted column> )
) AS <alias for the pivot table>
[ <optional ORDER BY clause> ]
[ ; ]
运算符的 UNPIVOT 语法。
SELECT [ <non-pivoted column> [ AS <column name> ] , ]
...
[ <output column for names of the pivot columns> [ AS <column name> ] , ]
[ <new output column created for values in result of the source query> [ AS <column name> ] ]
FROM
( <SELECT query that produces the data> )
AS <alias for the source query>
UNPIVOT
(
<new output column created for values in result of the source query>
FOR <output column for names of the pivot columns>
IN ( <first pivoted column>
, <second pivoted column>
, ... <last pivoted column> )
)
[ <optional ORDER BY clause> ]
[ ; ]
注解
UNPIVOT 子句中的列标识符需遵循目录排序规则。
对于Azure SQL 数据库，排序规则始终
SQL_Latin1_General_CP1_CI_AS为 。对于 SQL Server 部分包含的数据库，排序规则始终是
Latin1_General_100_CI_AS_KS_WS_SC。
如果将该列与与其他列合并，则需要 collate 子句 (COLLATE DATABASE_DEFAULT) 以避免冲突。
在 Microsoft Fabric 和 Azure Synapse Analytics 池中，如果GROUP BY存在非pivot 列输出，PIVOT则PIVOT具有运算符的查询将失败。 解决方法是从 .. 中删除非透视列 GROUP BY。 查询结果相同，因为此 GROUP BY 子句是重复的。
简单 PIVOT 示例
下面的代码示例生成一个两列四行的表。
USE AdventureWorks2022;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
结果集如下。
DaysToManufacture AverageCost
------------------ ------------
0 5.0885
1 223.88
2 359.1082
4 949.4105
未定义值为 <DaysToManufacturea0/a0> 的产品。
以下代码显示相同的结果，该结果经过透视以使 DaysToManufacture 值成为列标题。 即使结果为 NULL3（[3]） 天，也会提供列。
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS CostSortedByProductionDays,
[0], [1], [2], [3], [4]
FROM (
SELECT DaysToManufacture,
StandardCost
FROM Production.Product
) AS SourceTable
PIVOT (
AVG(StandardCost) FOR DaysToManufacture IN
([0], [1], [2], [3], [4])
) AS PivotTable;
结果集如下。
CostSortedByProductionDays 0 1 2 3 4
--------------------------- ----------- ----------- ----------- ----------- -----------
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
复杂 PIVOT 示例
若要生成交叉表报表来汇总数据，通常可能会发现 PIVOT 很有用。 例如，假设需要在 PurchaseOrderHeader 示例数据库中查询 AdventureWorks2022 表以确定由某些特定雇员所下的采购订单数。 以下查询提供了此报表（按供应商排序）。
USE AdventureWorks2022;
GO
SELECT VendorID,
[250] AS Emp1,
[251] AS Emp2,
[256] AS Emp3,
[257] AS Emp4,
[260] AS Emp5
FROM
(
SELECT PurchaseOrderID,
EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader
) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN ([250], [251], [256], [257], [260])
) AS pvt
ORDER BY pvt.VendorID;
以下为部分结果集。
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
----------- ----------- ----------- ----------- ----------- -----------
1492 2 5 4 4 4
1494 2 5 4 5 4
1496 2 4 4 5 5
1498 2 5 4 4 4
1500 3 4 4 5 4
将在 EmployeeID 列上透视此嵌套 select 语句返回的结果。
SELECT PurchaseOrderID,
EmployeeID,
VendorID
FROM PurchaseOrderHeader;
EmployeeID 列返回的唯一值变成了最终结果集中的字段。 因此，数据透视子句中指定的每个EmployeeID数字都有一列，即员工250、251员工256、和257260此示例。 PurchaseOrderID 列作为值列，将根据此列对最终输出中返回的列（称为分组列）进行分组。 在本例中，通过 COUNT 函数聚合分组列。 将显示一条警告消息，指示计算COUNT每个员工时，不会考虑列中出现PurchaseOrderID的任何 null 值。
重要
当聚合函数与 一起使用 PIVOT时，计算聚合时，不会考虑值列中存在任何 null 值。
UNPIVOT 示例
与 PIVOT 执行的操作几乎相反，UNPIVOT 将列轮换为行。 假设以上示例中生成的表在数据库中存储为 pvt，并且您需要将列标识符 Emp1、Emp2、Emp3、Emp4 和 Emp5 旋转为对应于特定供应商的行值。 因此，必须标识两个额外的列。
将调用Employee包含要旋转的列值的列值（Emp1Emp2等等），并调用Orders保存当前存在于要旋转的列下的值的列。 这些列分别对应于 Transact-SQL 定义中的 pivot_column 和 value_column。 查询如下。
-- Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (
VendorID INT,
Emp1 INT,
Emp2 INT,
Emp3 INT,
Emp4 INT,
Emp5 INT);
GO
INSERT INTO pvt
VALUES (1, 4, 3, 5, 4, 4);
INSERT INTO pvt
VALUES (2, 4, 1, 5, 5, 5);
INSERT INTO pvt
VALUES (3, 4, 3, 5, 4, 4);
INSERT INTO pvt
VALUES (4, 4, 2, 5, 5, 4);
INSERT INTO pvt
VALUES (5, 5, 1, 5, 5, 5);
GO
-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM (
SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt
) p
UNPIVOT
(
Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)
) AS unpvt;
GO
以下为部分结果集。
VendorID Employee Orders
----------- ----------- ------
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
UNPIVOT 不是完全相反的 PIVOT。 PIVOT 执行聚合，并将多个可能的行合并为输出中的一行。 UNPIVOT 不会重现原始表值表达式结果，因为行已合并。 此外， NULL 输出中消失的输入 UNPIVOT 中的值。 当值消失时，它显示操作之前PIVOT输入中可能存在原始NULL值。
AdventureWorks2022 示例数据库中的 Sales.vSalesPersonSalesByFiscalYears 视图将使用 PIVOT 返回每个销售人员在每个会计年度的总销售额。 若要在 SQL Server Management Studio 中编写视图脚本，请在“对象资源管理器”中的“视图”文件夹下找到 AdventureWorks2022 数据库对应的视图。 右键单击该视图名称，再选择“编写视图脚本为” 。