【SQL】PIVOT/UNPIVOTで行列変換する方法（MySQLでも実現）

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の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を工夫する必要があります。

ここでは、次のデータを使って行列変換してみましょう。

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による別名をつけて指定しましょう。

変換するのは次のデータです。

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をマスターしてください！