PIVOT と UNPIVOT の使用
関係演算子 PIVOT および UNPIVOT を使用すると、テーブル値式を別のテーブルに変更できます。PIVOT 関係演算子は、テーブル値式の中のある列から一意の値を取得して出力側の複数の列に変換することで式を行列変換し、最終的な出力のそれ以外の列値に必要な集計を行います。UNPIVOT 関係演算子の機能は PIVOT 関係演算子の逆で、テーブル値式の複数の列を列値に行列変換します。
.gif "注意") 注 |
|---|
SQL Server 2005 以降にアップグレードするデータベースに対して PIVOT および UNPIVOT を使用している場合は、データベースの互換性レベルを 90 以上に設定する必要があります。データベースの互換性レベルの設定方法の詳細については、「sp_dbcmptlevel (Transact-SQL)」を参照してください。 |
PIVOT の構文は、SELECT...CASE ステートメントを複雑に組み合わせて同じ操作を指定する場合よりも単純で読みやすくなります。PIVOT の構文の詳細な説明については、「FROM (Transact-SQL)」を参照してください。
PIVOT の構文を注釈付きで次に示します。
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>;
PIVOT の基本的な例
次のコード例では、4 行、2 列で構成されるテーブルを生成します。
USE AdventureWorks2008R2 ;
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
DaysToManufacture の値が 3 である製品が定義されていません。
次のコードでは、同じ結果が、DaysToManufacture 値を列見出しにピボット処理して表示されます。結果が NULL であっても、3 日目 [3] の列が表示されます。
-- 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;
以下に結果セットを示します。
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
PIVOT の複雑な例
PIVOT 関係演算子が役立つ一般的なシナリオは、データをまとめるためにクロス集計レポートを生成する場合です。たとえば、AdventureWorks2008R2 サンプル データベースの PurchaseOrderHeader テーブルにクエリを実行し、特定の従業員の発注数を抽出するとします。このレポートを仕入先別に返すクエリを次に示します。
USE AdventureWorks2008R2;
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 PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;
つまり、EmployeeID 列から返される一意の値そのものが、最終的な結果セットのフィールドになっています。その結果、PIVOT 句で指定した EmployeeID 番号 (この例では、164、198、223、231、および 233) ごとに列ができます。PurchaseOrderID 列は、最終的な出力に返される列をグループ化する (この列をグループ化列といいます) のための値列です。この例では、グループ化列を COUNT 関数で集計しています。従業員ごとに COUNT 関数を計算する際に PurchaseOrderID 列に表示されている NULL 値を無視したことを示す警告メッセージが表示されます。
.gif "重要な注意事項") 重要 |
|---|
PIVOT 関係演算子と集計関数を併用する場合、値列に存在する NULL 値は集計を実行する際に無視されます。 |
UNPIVOT 関係演算子で行われる操作は、基本的に PIVOT 演算子の逆で、列を行に変換します。上記の例で作成されたテーブルが pvt という名前でデータベースに保存されていて、列 ID Emp1、Emp2、Emp3、Emp4、および Emp5 を、特定の仕入先に対応する行の値に行列変換するとします。そのためには、さらに 2 つの列を指定する必要があります。行列変換する列値 (Emp1、Emp2、...) を格納する列を Employee といい、行列変換する列に現在格納されている値を保持する列を Orders といいます。この 2 つの列はそれぞれ 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 関係演算子を実行すると集計が行われ、出力では複数になる可能性のある行が 1 つの行にマージされます。UNPIVOT 関係演算子を実行しても、行が既にマージされているので、最初のテーブル値式の結果を再現することはできません。また、PIVOT 関係演算子を使用して操作する前にテーブルに NULL 値が存在していたり、UNPIVOT 関係演算子で操作するテーブルに NULL 値が存在していたとしても、もともとあった NULL は出力結果に含まれません。
AdventureWorks2008R2 サンプル データベースのビュー Sales.vSalesPersonSalesByFiscalYears では、PIVOT 関係演算子を使用して会計年度別に販売員ごとの総売上を返します。SQL Server Management Studio でビューをスクリプト化するには、オブジェクト エクスプローラーの AdventureWorks2008R2 データベースの [ビュー] フォルダーで、スクリプト化するビューを探します。ビュー名を右クリックし、[ビューをスクリプト化] をクリックします。