FROM - PIVOT および UNPIVOT の使用FROM - Using PIVOT and UNPIVOT

適用対象: ○SQL Server ○Azure SQL Database ○Azure SQL Data Warehouse ○Parallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

関係演算子 PIVOT および UNPIVOT を使用すると、テーブル値式を別のテーブルに変更できます。You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT では、式内の 1 つの列にある複数の一意の値を出力内の複数の列に変えることにより、テーブル値式が行列変換されます。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. 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_AS です。For SQL DatabaseSQL Database, the collation is always SQL_Latin1_General_CP1_CI_AS. SQL ServerSQL Server の部分的包含データベースの場合、照合順序は常に Latin1_General_100_CI_AS_KS_WS_SC です。For 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

次のコード例では、4 行、2 列で構成されるテーブルを生成します。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] であっても、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. たとえば、AdventureWorks2014 サンプル データベースの PurchaseOrderHeader テーブルにクエリを実行し、特定の従業員の発注数を抽出するとします。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. そのため、PIVOT 句で指定した EmployeeID 番号 (この例では、164198223231、および 233) ごとに列ができます。As 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 という名前でデータベースに保存されていて、列 ID Emp1Emp2Emp3Emp4、および Emp5 を、特定の仕入先に対応する行の値に行列変換するとします。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. そのため、さらに 2 つの列を指定する必要があります。As such, you must identify two additional columns. 行列変換する列値 (Emp1Emp2、...) を格納する列を Employee と呼び、行列変換する列に現在存在している値を保持する列を Orders と呼びます。The 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 関係演算子を実行すると集計が行われ、複数である可能性のある行が出力では 1 つの行にマージされます。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)