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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel 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, and runs aggregations where they're required on any left over column values that are wanted in the final output. UNPIVOT 執行的作業與 PIVOT 相反,它會將資料表值運算式的資料行旋轉成資料行值。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>;  

RemarksRemarks

UNPIVOT 子句中的資料行識別碼會依照目錄定序。The column identifiers in the UNPIVOT clause follow the catalog collation. SQL DatabaseSQL Database 而言,定序一律為 SQL_Latin1_General_CP1_CI_ASFor SQL DatabaseSQL 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 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)。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],還是為這三 NULL 天產生了一個資料行。A 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 資料行進行樞紐處理而來。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. 因此,樞紐子句指定的每個 EmployeeID 編號都會有一個資料行:在此情況下是員工 164198223231233As such, there's a column for each EmployeeID number specified in the pivot clause: in this case employees 164, 198, 223, 231, and 233. 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. 請注意,此時會顯示警告訊息,指出計算每個員工的 PurchaseOrderID 時,並未考慮 COUNT 資料行中出現的任何 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 使用彙總函數時,在計算彙總期間不會考慮值資料行中出現的任何 Null 值。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 執行的作業則幾乎與 PIVOT 相反,它會將資料行旋轉成資料列。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_columnvalue_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)