SQL ServerのPIVOT句・UNPIVOT句

こんにちは。SI部の安井と申します。

今回はSQL ServerのPIVOT句とUNPIVOT句を紹介します。

PIVOT・UNPIVOTは何をするものかというと、

• [PIVOT] 行を列に変換
• [UNPIVOT] 列を行に変換

というものです。

さっそく説明に移りたいと思います。

PIVOTの使い方

まずは、PIVOTの基本的な使い方からです。

PIVOT句の構文は下記のとおりです。

SELECT
	グループ化対象列の値1,
	グループ化対象列の値2,
	・・・
FROM テーブル名
PIVOT (
	集計関数(集計対象列)
	FOR グループ化対象列
	IN (グループ化対象列の値1, グループ化対象列の値2, ・・・)
) AS 別名

PIVOTを実行

それでは、実際に動かしてみましょう。

[商品名]と[売上金額]の2列からなる[売上」テーブルから、
[商品名]ごとのに列を作成し、[売上金額]の合計を集計します。

CREATE TABLE [売上]
(
	[商品名]     varchar(10),
	[売上金額]   int
);

INSERT INTO [売上] VALUES ('なし', 100);
INSERT INTO [売上] VALUES ('なし', 200);
INSERT INTO [売上] VALUES ('なし', 300);
INSERT INTO [売上] VALUES ('りんご', 200);
INSERT INTO [売上] VALUES ('りんご', 300);
INSERT INTO [売上] VALUES ('もも', 800);

SELECT
	[なし],
	[もも],
	[りんご]
FROM [売上]
PIVOT (SUM([売上金額]) FOR [商品名] IN ([なし], [もも], [りんご])) AS PV;

IN句に指定した[商品名]が列になり、
[売上金額]の合計が値となっています。

次は[売上]テーブルに[販売年月日]を追加し、
[販売年月日]ごとに[商品名]の[売上金額]の合計を集計します。

CREATE TABLE [売上]
(
	[販売年月日]  char(10),
	[商品名]      varchar(10),
	[売上金額]    int
);

INSERT INTO [売上] VALUES ('2014/10/15', 'なし', 100);
INSERT INTO [売上] VALUES ('2014/10/15', 'なし', 500);
INSERT INTO [売上] VALUES ('2014/11/18', 'なし', 200);
INSERT INTO [売上] VALUES ('2014/12/19', 'なし', 300);
INSERT INTO [売上] VALUES ('2014/11/15', 'りんご', 200);
INSERT INTO [売上] VALUES ('2014/11/17', 'りんご', 300);
INSERT INTO [売上] VALUES ('2014/10/19', 'もも', 800);
INSERT INTO [売上] VALUES ('2014/10/19', 'もも', 300);

SELECT
	[販売年月日],
	[なし],
	[もも],
	[りんご]
FROM [売上]
PIVOT (SUM([売上金額]) FOR [商品名] IN ([なし], [もも], [りんご])) AS PV;

実行結果を見ていただければわかると思いますが、
集計に使用した項目以外は、キー項目となります。

また、一般的なSQLと同じように絞り込み条件を指定することができます。
絞り込み条件はPIVOT句の後に記述します。
それでは、[販売年月日]が「10月」のレコードを抽出してみます。

SELECT
	[販売年月日],
	[なし],
	[もも],
	[りんご]
FROM [売上]
PIVOT (SUM([売上金額]) FOR [商品名] IN ([なし], [もも], [りんご])) AS PV
WHERE [販売年月日] LIKE '2014/10%';

以上がPIVOTの基本的な使い方です。

UNPIVOTの使い方

次にUNPIVOTの基本的な使い方を説明します。
UNPIVOTの構文は下記のとおりです。

SELECT
	指定した列を1つにまとめる列名,
	指定した列の値が入る列名
FROM テーブル名
UNPIVOT (
	指定した列の値が入る列名
	FOR 指定した列をまとめる列名
	IN (まとめる対象の列名1, まとめる対象の列名2 ・・・)
) AS 別名

UNPIVOTを実行

それではこちらも実際に動かしてみましょう。

[なし]・[もも]・[りんご]の3列からなる[売上]テーブルから、
[なし]・[もも]・[りんご]を[商品名]という列に、
それぞれの列の値を[売上金額]という列に集計します。

CREATE TABLE [売上]
(
	[なし]     int,
	[もも]     int,
	[りんご]   int
);

INSERT INTO [売上] VALUES (100, 0, 300);
INSERT INTO [売上] VALUES (400, 100, 200);

SELECT
	[商品名],
	[売上金額]
FROM [売上]
UNPIVOT ([売上金額] FOR [商品名] IN ([なし], [もも], [りんご])) AS UP

IN句に指定した列を[商品名]列の値に変換し、
IN句に指定した列の値が[売上金額]列の値となっています。

次はPIVOTと同じように、[売上]テーブルに[販売年月日]を追加し、
[販売年月日]・[商品名]・[売上金額]の3列に変換します。

CREATE TABLE [売上]
(
	[販売年月日]   varchar(10),
	[なし]        int,
	[もも]        int,
	[りんご]      int
);

INSERT INTO [売上] VALUES ('2014/10/15', 100, 0, 300);
INSERT INTO [売上] VALUES ('2014/10/15', 500, 200, 0);
INSERT INTO [売上] VALUES ('2014/10/19', 0, 0, 1100);
INSERT INTO [売上] VALUES ('2014/11/15', 0, 200, 0);
INSERT INTO [売上] VALUES ('2014/11/18', 200, 0, 0);
INSERT INTO [売上] VALUES ('2014/12/19', 300, 0, 0);

SELECT
	[販売年月日],
	[商品名],
	[売上金額]
FROM [売上]
UNPIVOT ([売上金額] FOR [商品名] IN ([なし], [もも], [りんご])) AS UP

UNPIVOTはPIVOTと違い、集計していないので、
キー項目は存在しません。

また、UNPIVOTも一般的なSQLと同じように絞り込み条件を指定することができます。
絞り込み条件はUNPIVOT句の後に記述します。
今回は[商品名]が「もも」のレコードを抽出してみます。

SELECT
	[販売年月日],
	[商品名],
	[売上金額]
FROM [売上]
UNPIVOT ([売上金額] FOR [商品名] IN ([なし], [もも], [りんご])) AS UP
WHERE [商品名] = 'もも'

以上がUNPIVOTの基本的な使い方です。

動的SQLの活用

PIVOTもUNPIVOTも、とても便利ですが、
どちらもIN句を動的に指定することができません。

そのため、先ほどPIVOTで説明したテーブルに、
[商品名]が「れもん」というレコードを追加しても、
取得することができません。

CREATE TABLE [売上]
(
	[販売年月日]     char(10),
	[商品名]         varchar(10),
	[売上金額]       int
);

INSERT INTO [売上] VALUES ('2014/10/15', 'なし', 100);
INSERT INTO [売上] VALUES ('2014/10/15', 'なし', 500);
INSERT INTO [売上] VALUES ('2014/11/18', 'なし', 200);
INSERT INTO [売上] VALUES ('2014/12/19', 'なし', 300);
INSERT INTO [売上] VALUES ('2014/11/15', 'りんご', 200);
INSERT INTO [売上] VALUES ('2014/11/17', 'りんご', 300);
INSERT INTO [売上] VALUES ('2014/10/19', 'もも', 800);
INSERT INTO [売上] VALUES ('2014/10/19', 'もも', 300);
INSERT INTO [売上] VALUES ('2014/10/15', 'れもん', 400);

SELECT
	[販売年月日],
	[なし],
	[もも],
	[りんご]
FROM [売上]
PIVOT (SUM([売上金額]) FOR [商品名] IN ([なし], [もも], [りんご])) AS PV;

「れもん」というレコードを取得したい場合は、
IN句に「れもん」という指定を追加する必要があります。

SELECT
	[販売年月日],
	[なし],
	[もも],
	[りんご],
	[れもん]
FROM [売上]
PIVOT (SUM([売上金額]) FOR [商品名] IN ([なし], [もも], [りんご], [れもん])) AS PV;

ただし、この方法ですと[商品名]の種類が増えるたびに、
SQLも修正しなくてはなりません。

種類が増えるたびに修正するのは現実的ではありません。
そのため、こういう場合は動的SQLを利用します。

動的SQLにすると、いくら種類が増えても修正する必要はなくなります。
上記のSQLを動的SQLに修正したものは下記のとおりになります。

CREATE TABLE [売上]
(
	[販売年月日]     char(10),
	[商品名]         varchar(10),
	[売上金額]       int
);

INSERT INTO [売上] VALUES ('2014/10/15', 'なし', 100);
INSERT INTO [売上] VALUES ('2014/10/15', 'なし', 500);
INSERT INTO [売上] VALUES ('2014/11/18', 'なし', 200);
INSERT INTO [売上] VALUES ('2014/12/19', 'なし', 300);
INSERT INTO [売上] VALUES ('2014/11/15', 'りんご', 200);
INSERT INTO [売上] VALUES ('2014/11/17', 'りんご', 300);
INSERT INTO [売上] VALUES ('2014/10/19', 'もも', 800);
INSERT INTO [売上] VALUES ('2014/10/19', 'もも', 300);
INSERT INTO [売上] VALUES ('2014/10/15', 'れもん', 400);

DECLARE @goods_list varchar(max)	-- 商品名のリスト(カンマ区切り)
DECLARE @sql nvarchar(max)		-- 動的SQL
SET @goods_list = null

-- 商品名リスト作成
SELECT
	@goods_list =
		CASE
			WHEN @goods_list IS NULL THEN '[' + [商品名] + ']'
			ELSE @goods_list + ', [' + [商品名] + ']'
		END
FROM [売上]
GROUP BY [商品名]

-- SQLクエリ作成
SET @sql =
	'SELECT * FROM [売上] PIVOT (SUM([売上金額]) FOR [商品名] in (' +
	@goods_list +
	')) AS PV'

-- SQLクエリの実行
EXEC sp_executesql @sql;

商品名リスト作成のSELECT文で、[売上]テーブルの[商品名]をカンマ区切りでつなげます。
先ほどのSQL文のIN句に、商品名リストを指定しSQL文を作成し実行します。

UNPIVOTも[売上]テーブルに[れもん]列が増えた場合、
IN句に[れもん]を指定するか、PIVOTの動的SQLに似たようなものを作成する必要があります。

UNPIVOTは列名を指定するので、カタログビューを使用します。
UNPIVOT用の動的SQLは下記のとおりです。

CREATE TABLE [売上]
(
	[販売年月日]     varchar(10),
	[りんご]        int,
	[なし]          int,
	[もも]          int,
	[れもん]        int
);

INSERT INTO [売上] VALUES ('2014/10/15', 100, 0, 300, 300);
INSERT INTO [売上] VALUES ('2014/10/15', 500, 200, 0, 100);
INSERT INTO [売上] VALUES ('2014/10/19', 0, 0, 1100, 0);
INSERT INTO [売上] VALUES ('2014/11/15', 0, 200, 0, 0);
INSERT INTO [売上] VALUES ('2014/11/18', 200, 0, 0, 0);
INSERT INTO [売上] VALUES ('2014/12/19', 300, 0, 0, 0);

DECLARE @column_list varchar(max)
DECLARE @sql nvarchar(max)
SET @column_list = null

-- 列名リスト作成
SELECT
	@column_list =
		CASE
			WHEN @column_list IS NULL THEN '[' + B.name + ']'
			ELSE @column_list + ', [' + B.name + ']'
		END
FROM sys.tables A
INNER JOIN sys.columns B
ON A.object_id = B.object_id
WHERE A.object_id = OBJECT_ID('売上')
AND B.system_type_id = 56	-- int項目を指定

-- SQLクエリ作成
SET @sql =
	'SELECT * FROM [売上] UNPIVOT ([売上金額] FOR [商品名] IN (' +
	@column_list +
	')) AS UP'

-- SQL文実行
EXEC sp_executesql @sql

カタログビューより、int型の列名をカンマ区切りでつなげます。
UNPIVOTのSQL文のIN句に、列名リストを指定しSQL文を作成し実行します。

最後に

PIVOTやUNPIVOTを利用すると、今までとは別の視点で表を活用することができます。

エクセルに貼り付けたり、プログラムを組むことなくSQLだけでクロス集計をおこうなうことができるのも大きな魅力ではないでしょうか。

皆さんもぜひ試してみてください。