FROM - 使用 PIVOT 和 UNPIVOTFROM - Using PIVOT and UNPIVOT

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

可以使用 PIVOTUNPIVOT 关系运算符将表值表达式更改为另一个表。You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT 通过将表达式中的一个列的唯一值转换为输出中的多列,来轮替表值表达式。PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. PIVOT 在需要对最终输出所需的所有剩余列值执行聚合时运行聚合。And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output. 与 PIVOT 执行的操作相反,UNPIVOT 将表值表达式的列轮换为列值。UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements. 有关 PIVOT 语法的完整说明,请参阅 FROM (Transact-SQL)For a complete description of the syntax for PIVOT, see FROM (Transact-SQL).

语法Syntax

以下语法总结了如何使用 PIVOT 运算符。The following syntax summarizes how to use the PIVOT operator.

SELECT <non-pivoted column>,  
    [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 will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;  

备注Remarks

UNPIVOT 子句中的列标识符需遵循目录排序规则。The column identifiers in the UNPIVOT clause follow the catalog collation. 对于 SQL 数据库SQL Database,排序规则始终是 SQL_Latin1_General_CP1_CI_ASFor SQL 数据库SQL Database, the collation is always SQL_Latin1_General_CP1_CI_AS. 对于 SQL ServerSQL Server 部分包含的数据库,排序规则始终是 Latin1_General_100_CI_AS_KS_WS_SCFor SQL ServerSQL Server partially contained databases, the collation is always Latin1_General_100_CI_AS_KS_WS_SC. 如果将该列与与其他列合并,则需要 collate 子句 (COLLATE DATABASE_DEFAULT) 以避免冲突。If the column is combined with other columns, then a collate clause (COLLATE DATABASE_DEFAULT) is required to avoid conflicts.

简单 PIVOT 示例Basic PIVOT Example

下面的代码示例生成一个两列四行的表。The following code example produces a two-column table that has four rows.

USE AdventureWorks2014 ;  
GO  
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost   
FROM Production.Product  
GROUP BY DaysToManufacture;  

下面是结果集:Here is the result set.

DaysToManufacture AverageCost
----------------- -----------
0                 5.0885
1                 223.88
2                 359.1082
4                 949.4105

没有定义 DaysToManufacture 为 3 的产品。No products are defined with three DaysToManufacture.

以下代码显示相同的结果,该结果经过透视以使 DaysToManufacture 值成为列标题。The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. 提供一个列表示三 [3] 天,即使结果为 NULLA column is provided for three [3] days, even though the results are NULL.

-- Pivot table with one row and five columns  
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,   
[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;  
  

下面是结果集:Here is the result set.

Cost_Sorted_By_Production_Days 0           1           2           3           4         
------------------------------ ----------- ----------- ----------- ----------- -----------
AverageCost                    5.0885      223.88      359.1082    NULL        949.4105

复杂 PIVOT 示例Complex PIVOT Example

若要生成交叉表报表来汇总数据,通常可能会发现 PIVOT 很有用。A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to give a summary of the data. 例如,假设需要在 PurchaseOrderHeader 示例数据库中查询 AdventureWorks2014 表以确定由某些特定雇员所下的采购订单数。For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks2014 sample database to determine the number of purchase orders placed by certain employees. 以下查询提供了此报表(按供应商排序)。The following query provides this report, ordered by vendor.

USE AdventureWorks2014;  
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;  

以下为部分结果集。Here is a partial result set.

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 语句返回的结果。The results returned by this subselect statement are pivoted on the EmployeeID column.

SELECT PurchaseOrderID, EmployeeID, VendorID  
FROM PurchaseOrderHeader;  

EmployeeID 列返回的唯一值变成了最终结果集中的字段。The unique values returned by the EmployeeID column become fields in the final result set. 因此,在 pivot 子句中指定的每个 EmployeeID 号都有对应的列:在此示例中,为员工 250251256257260As such, there's a column for each EmployeeID number specified in the pivot clause: in this case employees 250, 251, 256, 257, and 260. PurchaseOrderID 列作为值列,将根据此列对最终输出中返回的列(称为分组列)进行分组。The PurchaseOrderID column serves as the value column, against which the columns returned in the final output, which are called the grouping columns, are grouped. 在本例中,通过 COUNT 函数聚合分组列。In this case, the grouping columns are aggregated by the COUNT function. 请注意,系统会显示警告消息,以指明在为每个员工计算 COUNT 时,未考虑 PurchaseOrderID 列中的任何 NULL 值。Notice that a warning message appears that indicates that any null values appearing in the PurchaseOrderID column weren't considered when computing the COUNT for each employee.

重要

如果聚合函数与 PIVOT 一起使用,则计算聚合时将不考虑出现在值列中的任何空值。When aggregate functions are used with PIVOT, the presence of any null values in the value column are not considered when computing an aggregation.

逆透视示例UNPIVOT Example

PIVOT 执行的操作几乎相反,UNPIVOT 将列轮换为行。UNPIVOT carries out almost the reverse operation of PIVOT, by rotating columns into rows. 假设以上示例中生成的表在数据库中存储为 pvt,并且您需要将列标识符 Emp1Emp2Emp3Emp4Emp5 旋转为对应于特定供应商的行值。Suppose the table produced in the previous example is stored in the database as pvt, and you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, and Emp5 into row values that correspond to a particular vendor. 因此,必须标识另外两个列。As such, you must identify two additional columns. 包含要轮换的列值(Emp1Emp2...)的列称为 Employee,保留要轮换列下的现有值的列称为 OrdersThe column that will contain the column values that you're rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently exist under the columns being rotated will be called Orders. 这些列分别对应于 Transact-SQLTransact-SQL 定义中的 pivot_column 和 value_column 。These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQLTransact-SQL definition. 以下为该查询。Here is the query.

-- 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  

以下为部分结果集。Here is a partial result set.

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 的逆操作。Notice that UNPIVOT isn't the exact reverse of PIVOT. PIVOT 执行聚合,并将多个可能的行合并为输出中的一行。PIVOT carries out an aggregation and merges possible multiple rows into a single row in the output. UNPIVOT 不重现原始表值表达式的结果,因为行已被合并。UNPIVOT doesn't reproduce the original table-valued expression result because rows have been merged. 另外,UNPIVOT 输入中的 NULL 值也在输出中消失了。Also, null values in the input of UNPIVOT disappear in the output. 如果值消失,表明在执行 PIVOT 操作前,输入中可能就已存在原始 NULL 值。When the values disappear, it shows that there may have been original null values in the input before the PIVOT operation.

AdventureWorks2012AdventureWorks2012 示例数据库中的 Sales.vSalesPersonSalesByFiscalYears 视图将使用 PIVOT 返回每个销售人员在每个会计年度的总销售额。The Sales.vSalesPersonSalesByFiscalYears view in the AdventureWorks2012AdventureWorks2012 sample database uses PIVOT to return the total sales for each salesperson, for each fiscal year. 若要在 SQL Server Management StudioSQL Server Management Studio 中编写视图脚本,请在“对象资源管理器”中的“视图”文件夹下找到 AdventureWorks2012AdventureWorks2012 数据库对应的视图 。To script the view in SQL Server Management StudioSQL Server Management Studio, in Object Explorer, locate the view under the Views folder for the AdventureWorks2012AdventureWorks2012 database. 右键单击该视图名称,再选择“编写视图脚本为” 。Right-click the view name, and then select Script View as.

另请参阅See Also

FROM (Transact-SQL) FROM (Transact-SQL)
CASE (Transact-SQL)CASE (Transact-SQL)