FROM - 使用 PIVOT 和 UNPIVOT
适用于：
SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)
可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。 PIVOT 通过将表达式中的一个列的唯一值转换为输出中的多列，来轮替表值表达式。 PIVOT 还会运行聚合，在这些聚合中，它们需要针对最终输出中所需的任何剩余列值。 UNPIVOT 通过将表值表达式的列旋转为列值来执行相反的操作 PIVOT。
与在复杂语句系列SELECT...CASE中指定的语法相比，该PIVOT语法更容易且更易于阅读。 有关语法 PIVOT的完整说明，请参阅 FROM 子句。
本文中的 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 数据库对应的视图。 右键单击该视图名称，再选择“编写视图脚本为” 。