テーブルにレコードとして保存されているデータを取得する際に横に展開して1行にしたい場合があります。
例えば月ごとに登録されている金額データを1月～12月の列として取得したい場合などです。
今回は縦持ちになっているデータをSQLで横に展開して取得する方法を紹介します。
テーブルとデータの準備
レコードとして保持されているデータを横に展開するサンプル作成のためのテーブルを作成します。
テーブルは四半期ごとの売上金額を管理するQUARTERLY_SALESという名前で作成します。
列には「売上年」を保持する「SALES_YEAR」、四半期を表す1～4までの値の「四半期番号」を保持する「QUARTER_NO」、「売上金額」を保持する「SALES_AMOUNT」の3つにします。
| No | 列名 | データ型 |
|---|---|---|
| 1 | SALES_YEAR | INT |
| 2 | QUARTER_NO | INT |
| 3 | SALES_AMOUNT | DECIMAL(12) |
テーブルのCREATEとデータのINSERT
テーブルを作成するSQLとデータを挿入するSQLを以下に記載します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- テーブルの作成 CREATE TABLE QUARTERLY_SALES ( SALES_YEAR INT NOT NULL, QUARTER_NO INT NOT NULL, SALES_AMOUNT DECIMAL(12, 0) NOT NULL, PRIMARY KEY (SALES_YEAR, QUARTER_NO) ); -- データの挿入 INSERT INTO QUARTERLY_SALES (SALES_YEAR, QUARTER_NO, SALES_AMOUNT) VALUES (2017, 1, 1524643000), (2017, 2, 2048956000), (2017, 3, 1985745000), (2017, 4, 1635581000), (2018, 1, 1402768000), (2018, 2, 1915046000), (2018, 3, 1833705000), (2018, 4, 1791288000); |
上記のQUARTERLY_SALESテーブルへのデータのインサートは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法については、以下の記事を参照してください。
データの取得
まずはそのままデータを取得してみます。
|
1 2 3 4 5 6 7 8 9 |
SELECT SALES_YEAR, QUARTER_NO, SALES_AMOUNT FROM QUARTERLY_SALES ORDER BY SALES_YEAR, QUARTER_NO |
売上年と四半期ごとのデータ（8レコード）が取得できました。
横展開して取得
QUARTERLY_SALESテーブルのデータを売上年1レコードに対して売上金額を四半期ごとに4列にして全部で5列にします。
取得する列は以下になります。
- 売上年
- 第1四半期の売上金額
- 第2四半期の売上金額
- 第3四半期の売上金額
- 第4四半期の売上金額
縦持ちのデータを横に展開して取得するにはCASE式を使用します。
CASE式の条件に横に展開する条件を記述して、条件を満たす場合は値を設定し、条件を満たさない場合は無効な値（NULLや0など）を設定します。
QUARTERLY_SALESテーブルのデータを横に展開して取得するSQLは次のようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT SALES_YEAR, -- 四半期番号が「1」の場合に売上金額を取得 CASE QUARTER_NO WHEN 1 THEN SALES_AMOUNT ELSE NULL END AS SELES_AMOUNT_1, -- 四半期番号が「2」の場合に売上金額を取得 CASE QUARTER_NO WHEN 2 THEN SALES_AMOUNT ELSE NULL END AS SELES_AMOUNT_2, -- 四半期番号が「3」の場合に売上金額を取得 CASE QUARTER_NO WHEN 3 THEN SALES_AMOUNT ELSE NULL END AS SELES_AMOUNT_3, -- 四半期番号が「4」の場合に売上金額を取得 CASE QUARTER_NO WHEN 4 THEN SALES_AMOUNT ELSE NULL END AS SELES_AMOUNT_4 FROM QUARTERLY_SALES ORDER BY SALES_YEAR |
上記のSQLではCASE式で四半期番号の「QUARTER_NO」が1～4のそれぞれの場合を条件式にして、条件を満たす場合は売上金額の「SALES_AMOUNT」に、そうでない場合はNULLにしています。（3～10行目）
これを実行すると以下のようなデータが取得されます。
取得結果を見ると売上年と四半期ごとの8レコードが取得され、各レコードごとに四半期番号の「QUARTER_NO」に一致する場合のみ売上金額が取得されています。
上記の結果をもとに8レコード取得されているデータを各売上年ごとに1レコードの2レコードにします。
売上年ごとの2レコードにするためにはGROUP BY句を使用します。
売上年ごとのレコードを取得するのでGROUP BY句に売上年のSALES_YEARをしてみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT SALES_YEAR, -- 四半期番号が「1」の場合に売上金額を取得 CASE QUARTER_NO WHEN 1 THEN SALES_AMOUNT ELSE NULL END AS SELES_AMOUNT_1, -- 四半期番号が「2」の場合に売上金額を取得 CASE QUARTER_NO WHEN 2 THEN SALES_AMOUNT ELSE NULL END AS SELES_AMOUNT_2, -- 四半期番号が「3」の場合に売上金額を取得 CASE QUARTER_NO WHEN 3 THEN SALES_AMOUNT ELSE NULL END AS SELES_AMOUNT_3, -- 四半期番号が「4」の場合に売上金額を取得 CASE QUARTER_NO WHEN 4 THEN SALES_AMOUNT ELSE NULL END AS SELES_AMOUNT_4 FROM QUARTERLY_SALES GROUP BY SALES_YEAR ORDER BY SALES_YEAR |
四半期番号の「QUARTER_NO」と、売上金額の「SALES_AMOUNT」がSQL内で参照されているので、GROUP BY句に含めろと怒られてしまいます。
四半期番号の「QUARTER_NO」と、売上金額の「SALES_AMOUNT」をGROUP BY句に入れてしまうとGROUP BYで集計しない場合と同じ結果になるのでGROUP BY句に含めるのではなく集計関数のSUM関数を使って売上金額の「SALES_AMOUNT」を集計します。
CASE式を使用している行にSUM関数を使って以下のようにします。
|
1 2 |
-- CASE式の行にSUM関数を適用する例 SUM(CASE QUARTER_NO WHEN 1 THEN SALES_AMOUNT ELSE NULL END) |
CASE～ENDまでの部分をSUM関数で囲って合計を取得します。こうすることによって四半期ごとに4レコードに分かれて行を1レコードに集約することができます。
以下がSUM関数を適用して集計するSQLになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT SALES_YEAR, SUM(CASE QUARTER_NO WHEN 1 THEN SALES_AMOUNT ELSE 0 END) AS SELES_AMOUNT_1, SUM(CASE QUARTER_NO WHEN 2 THEN SALES_AMOUNT ELSE 0 END) AS SELES_AMOUNT_2, SUM(CASE QUARTER_NO WHEN 3 THEN SALES_AMOUNT ELSE 0 END) AS SELES_AMOUNT_3, SUM(CASE QUARTER_NO WHEN 4 THEN SALES_AMOUNT ELSE 0 END) AS SELES_AMOUNT_4, SUM(SALES_AMOUNT) AS SELES_AMOUNT_TOTAL FROM QUARTERLY_SALES GROUP BY SALES_YEAR ORDER BY SALES_YEAR |
上記のSQLではCASE式のすべての条件に合わない場合にSUM関数の集計結果がNULLにならないようにCASE式のELSEの部分に「NULL」ではなく「0」を指定しています。
また、売上年ごとの合計の売上金額を取得するために全体をSUM関数で集計した列のSELES_AMOUNT_TOTALを追加しています。
横に展開する際のキーとなる値（上記の例では四半期番号の「QUARTER_NO」）が決まっている場合はCASE式を使用してPIVOTのような集計ができます。
SQLServerであればCASE式の代わりにIIF関数を使うことで同じ結果を得ることもできます。
最後にIIF関数を使用したQUARTERLY_SALESテーブルのデータを横に展開して取得するSQLを記載しておきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- CASE式の代わりにIIF関数を使った横展開集計 SELECT SALES_YEAR, SUM(IIF(QUARTER_NO = 1, SALES_AMOUNT, 0)) AS SELES_AMOUNT_1, SUM(IIF(QUARTER_NO = 2, SALES_AMOUNT, 0)) AS SELES_AMOUNT_2, SUM(IIF(QUARTER_NO = 3, SALES_AMOUNT, 0)) AS SELES_AMOUNT_3, SUM(IIF(QUARTER_NO = 4, SALES_AMOUNT, 0)) AS SELES_AMOUNT_4, SUM(SALES_AMOUNT) AS SELES_AMOUNT_TOTAL FROM QUARTERLY_SALES GROUP BY SALES_YEAR ORDER BY SALES_YEAR |
取得結果はCASE式を使用した場合と同じになります。
