【SQL】PIVOT/UNPIVOTで行列変換する方法(MySQLでも実現)
  • facebookページ
  • twitterページ
  • 2020.05.22

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

    PIVOTとは

    PIVOTとは、テーブル値式の1つの列にある一意の値を取得し、出力内の複数の列に変換することにより、テーブル値式を行列変換する関係演算子です。

    少し複雑な説明になってしまいましたが、簡単に説明すると「行を列に変換」してくれます。

    PIVOTで集計が実行されるのは、最終出力で必要な残りの任意の列値に対して集計が必要な場合です。

    実際にPIVOTの使い方を見て確認していきましょう。

    PIVOTの使い方

    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は次のように記述します。

    IN句で指定したnameが列になり、scoreの平均値が値となったpivot_tableができました。

    UNPIVOTの使い方

    UNPIVOTは、PIVOTの逆でテーブル値式の列を行に変換します。

    こちらも使い方を見ていきましょう。

    サンプルで動きを確認してみましょう。

    my_tableテーブルに、s1・s2・s3の3列を用意します。

    そして、s1・s2・s3をname列に、各列の値をscore列に集計します。

    IN句に指定したs1〜s3列をname列の値に変換し、IN句に指定したそれぞれの列の値がscore列の値となっているのがわかります。

    PIVOTで合計を求める

    PIVOTを使って列ごとの合計を求めてみましょう。

    合計を求める場合は「SUM」を使います。

    先ほどの「PIVOTの使い方」で使用したテーブルで確認してみましょう。

    列ごとの合計を表示できるはずです。

    PIVOTで動的SQLを活用する

    行列の変換ができるPIVOT、UNPIVOTは非常に便利な機能ですが、このままだとIN句の指定は静的なものだけに限られてしまいます。

    例えば、先ほどのテーブルのnameに「s4」のレコードを追加しても、s4のレコードは取得できません。

    新しいnameが追加されるごとに指定を追加してもよいのですが、SQLを修正する必要があるので少し不便です。

    そのため、レコードの追加に合わせて動的に指定を変更したい場合は「動的SQL」を活用しましょう。

    動的SQLを使うと、nameの種類が増えても修正する必要がありません。

    PIVOTの使い方で使用したSQLを、動的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の部分で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

    まとめ

    SQLのPIVOTの使い方を解説しました。

    PIVOTは行列変換ができる便利な機能です。

    SQLは少し複雑になりますが、ぜひこの記事を参考にPIVOTをマスターしてください!



    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE