目次
PIVOTとは
PIVOTとは、テーブル値式の1つの列にある一意の値を取得し、出力内の複数の列に変換することにより、テーブル値式を行列変換する関係演算子です。
少し複雑な説明になってしまいましたが、簡単に説明すると「行を列に変換」してくれます。
PIVOTで集計が実行されるのは、最終出力で必要な残りの任意の列値に対して集計が必要な場合です。
実際にPIVOTの使い方を見て確認していきましょう。
PIVOTの使い方
PIVOTの基本的な使い方・構文は次のとおりです。
SELECT
PIVOT列の値1,
PIVOT列の値2,
PIVOT列の値3,
・・・
FROM テーブル名
PIVOT (
集計関数(集計対象列)
FOR PIVOT列
IN (PIVOT列の値1, PIVOT列の値2, PIVOT列の値3, ・・・)
) AS PIVOTテーブルの別名
サンプルで動きを確認してみましょう。
my_tableには、次のデータがあると仮定します。
| name | score |
|---|---|
| s1 | 126 |
| s2 | 220 |
| s3 | 60 |
| s2 | 500 |
| s1 | 270 |
| s1 | 388 |
| s3 | 200 |
nameとscoreの2列からなるmy_tableテーブルをもとに、PIVOTでnameごとに列を作成し、scoreの平均値を集計しましょう。
PIVOTは次のように記述します。
SELECT
s1,
s2,
s3
FROM my_table
PIVOT (AVG(score) FOR name IN (s1, s2, s3)) AS pivot_table
s1 s2 s3
----------- ----------- -----------
238 470 130
IN句で指定したnameが列になり、scoreの平均値が値となったpivot_tableができました。
UNPIVOTの使い方
UNPIVOTは、PIVOTの逆でテーブル値式の列を行に変換します。
こちらも使い方を見ていきましょう。
SELECT
UNPIVOT列をまとめる列,
UNPIVOT列の値が入る列
FROM テーブル名
UNPIVOT (
UNPIVOT列の値が入る列
FOR UNPIVOT列をまとめる列
IN (UNPIVOT列の値1, UNPIVOT列の値2, UNPIVOT列の値3 ・・・)
) AS UNPIVOTテーブルの別名
サンプルで動きを確認してみましょう。
my_tableテーブルに、s1・s2・s3の3列を用意します。
そして、s1・s2・s3をname列に、各列の値をscore列に集計します。
SELECT
name,
score
FROM my_table
UNPIVOT (score FOR name IN (s1, s2, s3)) AS unpivot_table;
name score
----------- -----------
s1 126
s2 220
s3 60
s2 500
s1 270
s1 388
s3 200
IN句に指定したs1〜s3列をname列の値に変換し、IN句に指定したそれぞれの列の値がscore列の値となっているのがわかります。
PIVOTで合計を求める
PIVOTを使って列ごとの合計を求めてみましょう。
合計を求める場合は「SUM」を使います。
先ほどの「PIVOTの使い方」で使用したテーブルで確認してみましょう。
SELECT
s1,
s2,
s3
FROM my_table
PIVOT (SUM(score) FOR name IN (s1, s2, s3)) AS pivot_table;
s1 s2 s3
----------- ----------- -----------
784 720 260
列ごとの合計を表示できるはずです。
PIVOTで動的SQLを活用する
行列の変換ができるPIVOT、UNPIVOTは非常に便利な機能ですが、このままだとIN句の指定は静的なものだけに限られてしまいます。
例えば、先ほどのテーブルのnameに「s4」のレコードを追加しても、s4のレコードは取得できません。
新しいnameが追加されるごとに指定を追加してもよいのですが、SQLを修正する必要があるので少し不便です。
そのため、レコードの追加に合わせて動的に指定を変更したい場合は「動的SQL」を活用しましょう。
動的SQLを使うと、nameの種類が増えても修正する必要がありません。
PIVOTの使い方で使用したSQLを、動的SQLに変更する場合は次のようになります。
DECLARE @score_list varchar(max)
DECLARE @sql nvarchar(max)
SET @score_list = null
SELECT
@score_list =
CASE
WHEN @score_list IS NULL THEN '[' + name + ']'
ELSE @score_list + ', [' + name + ']'
END
FROM my_table
GROUP BY name
SET @sql =
'SELECT * FROM my_table PIVOT (AVG(score) FOR name in (' +
@score_list +
')) AS pivot_table'
EXEC sp_executesql @sql;
@score_listを作成するSELECT文で、my_tableテーブルにあるnameをカンマ区切りでつなげています。
そして、SQL文のIN句に商品名@score_listを指定し、実行すれば動的にnameとその値のレコードを取得できます。
MySQLでpivotを実現
MySQLではPIVOTを使用することができません。
そのためPIVOT（行列変換）を実現するには、SQLを工夫する必要があります。
ここでは、次のデータを使って行列変換してみましょう。
| id | type_id | type_name | value |
|---|---|---|---|
| 1 | 1 | name | Satou |
| 1 | 2 | age | 25 |
| 1 | 3 | sex | Male |
| 2 | 1 | name | Takahashi |
| 2 | 2 | age | 33 |
| 2 | 3 | sex | Female |
| 3 | 1 | name | Abe |
| 3 | 2 | age | 29 |
| 3 | 3 | sex | Male |
SELECT id,
(SELECT value FROM my_table WHERE type_name = 'name' AND id = m.id) AS name,
(SELECT value FROM my_table WHERE type_name = 'age' AND id = m.id) AS age,
(SELECT value FROM my_table WHERE type_name = 'sex' AND id = m.id) AS sex FROM my_table m
GROUP BY id;
id name age sex
----------- ----------- ----------- -----------
1 Satou 25 Male
2 Takahashi 33 FeMale
3 Abe 29 Male
SELECTの部分でname、age、sexごとに列を作成し、idごとにGROUP BYで並べます。
PIVOTで複数列変換する
PIVOTで複数列を行列変換する際は、INの中の指定でASによる別名をつけて指定しましょう。
変換するのは次のデータです。
| id | score | time |
|---|---|---|
| s1 | 126 | 60 |
| s2 | 220 | 100 |
| s3 | 60 | 25 |
| s2 | 500 | 260 |
| s1 | 270 | 120 |
| s1 | 388 | 175 |
| s3 | 200 | 140 |
SELECT
pv.S1_score,
pv.S1_time,
pv.S2_score,
pv.S2_time,
pv.S3_score,
pv.S3_time
FROM (
SELECT id,
SUM(score) AS score,
SUM(time) AS time
FROM my_table
GROUP BY id
)
PIVOT (
SUM(score) AS score,
SUM(time) AS time
FOR コード IN (
's1' AS S1,
's2' AS S2,
's3' AS S3
)
) pv
S1_score S1_time S2_score S2_time S3_score S3_time
-------------- ---------------- -------------- --------------- --------------- ---------------
784 355 720 360 260 165
まとめ
SQLのPIVOTの使い方を解説しました。
PIVOTは行列変換ができる便利な機能です。
SQLは少し複雑になりますが、ぜひこの記事を参考にPIVOTをマスターしてください！
