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 performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT 執行的作業與 PIVOT 相反,它會將資料表值運算式的資料行旋轉成資料行值。UNPIVOT performs 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 Server]SQL Server 部分自主資料庫而言,定序一律為 Latin1_General_100_CI_AS_KS_WS_SCFor [SQL Server]SQL 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 summarize 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 資料行所傳回的唯一值本身會變成最終結果集中的欄位。This means that the unique values returned by the EmployeeID column themselves become fields in the final result set. 因此,樞紐子句指定的每個 EmployeeID 編號都會有一個資料行:在此情況下,是以下員工 164198223231233Therefore, there is 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 were not 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 performs 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. 這表示您必須識別兩個額外的資料行。This means that you must identify two additional columns. 包含所要旋轉的資料行值 (Emp1Emp2、...) 的資料行將命名為 Employee,而保留目前位在所要旋轉資料行之下的值的資料行則命名為 OrdersThe column that will contain the column values that you are rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently reside 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 is not the exact reverse of PIVOT. PIVOT 會執行彙總,因此會將多個可能的資料列合併成輸出中的單一資料列。PIVOT performs an aggregation and, therefore, merges possible multiple rows into a single row in the output. 由於資料列已經合併,因此 UNPIVOT 並不會重新產生原始資料表值運算式結果。UNPIVOT does not reproduce the original table-valued expression result because rows have been merged. 此外,在 UNPIVOT 輸入中的 Null 值在輸出中會消失不見,但是在執行 PIVOT 作業之前,輸入中原先可能有原始 Null 值。Besides, null values in the input of UNPIVOT disappear in the output, whereas 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. 若要在 Transact-SQLSQL Server Management Studio 中編寫該檢視表的指令碼,請在 [物件總管] 中,於 AdventureWorks2012AdventureWorks2012 資料庫的 [檢視表] 資料夾底下找出該檢視表。To script the view in Transact-SQLSQL 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)