【Oracle】UNPIVOTの使い方完全ガイド｜横持ちデータを縦持ちに変換・INCLUDE NULLS・複数列・PIVOT組み合わせまで解説

-- monthly_sales テーブル（横持ち）
SELECT * FROM monthly_sales;

-- 結果イメージ
-- DEPT_ID  Q1_SALES  Q2_SALES  Q3_SALES  Q4_SALES
-- -------  --------  --------  --------  --------
-- 10       1200000   1350000   980000    1500000
-- 20       850000    920000    770000    1100000

-- DEPT_ID  QUARTER   SALES
-- -------  -------   -------
-- 10       Q1_SALES  1200000
-- 10       Q2_SALES  1350000
-- 10       Q3_SALES  980000
-- 10       Q4_SALES  1500000
-- 20       Q1_SALES  850000
-- ...

SELECT <列リスト>
FROM <テーブルまたはサブクエリ>
UNPIVOT [INCLUDE NULLS | EXCLUDE NULLS] (
    <値列の別名>            -- 変換後の値が入る列名
    FOR <種別列の別名>      -- 変換後の元列名が入る列名
    IN (
        <列名1> [AS '表示名1'],
        <列名2> [AS '表示名2'],
        ...
    )
);

-- テーブル定義と初期データ
CREATE TABLE monthly_sales (
    dept_id   NUMBER,
    q1_sales  NUMBER,
    q2_sales  NUMBER,
    q3_sales  NUMBER,
    q4_sales  NUMBER
);

INSERT INTO monthly_sales VALUES (10, 1200000, 1350000,  980000, 1500000);
INSERT INTO monthly_sales VALUES (20,  850000,  920000,  770000, 1100000);
INSERT INTO monthly_sales VALUES (30, 2100000, 1980000, 2250000, 2400000);
COMMIT;

-- UNPIVOT で縦持ちに変換
SELECT dept_id, quarter, sales
FROM monthly_sales
UNPIVOT (
    sales           -- 変換後の値列名
    FOR quarter     -- 変換後の種別列名
    IN (
        q1_sales AS 'Q1',
        q2_sales AS 'Q2',
        q3_sales AS 'Q3',
        q4_sales AS 'Q4'
    )
)
ORDER BY dept_id, quarter;

-- DEPT_ID  QUARTER  SALES
-- -------  -------  -------
-- 10       Q1       1200000
-- 10       Q2       1350000
-- 10       Q3        980000
-- 10       Q4       1500000
-- 20       Q1        850000
-- 20       Q2        920000
-- 20       Q3        770000
-- 20       Q4       1100000
-- 30       Q1       2100000
-- 30       Q2       1980000
-- 30       Q3       2250000
-- 30       Q4       2400000

-- NULL データを含むサンプル
CREATE TABLE score_data (
    student_id  NUMBER,
    math_score  NUMBER,
    eng_score   NUMBER,
    sci_score   NUMBER
);
INSERT INTO score_data VALUES (1, 85, 90,  NULL);  -- 理科未受験
INSERT INTO score_data VALUES (2, 70, NULL, 88);   -- 英語未受験
INSERT INTO score_data VALUES (3, 92, 78,  95);
COMMIT;

-- デフォルト（EXCLUDE NULLS）: NULL 行が除外される
SELECT student_id, subject, score
FROM score_data
UNPIVOT (
    score FOR subject
    IN (math_score AS '数学', eng_score AS '英語', sci_score AS '理科')
)
ORDER BY student_id, subject;
-- → student_id=1 の理科、student_id=2 の英語は結果に出ない

-- INCLUDE NULLS: NULL 行も結果に含める
SELECT student_id, subject, score
FROM score_data
UNPIVOT INCLUDE NULLS (
    score FOR subject
    IN (math_score AS '数学', eng_score AS '英語', sci_score AS '理科')
)
ORDER BY student_id, subject;
-- → NULL 行も結果に含まれる（score = NULL として出力）

SELECT <列リスト>
FROM <テーブル>
UNPIVOT (
    (<値列1>, <値列2>)         -- 展開する値列を () でまとめる
    FOR <種別列名>
    IN (
        (<元列A_1>, <元列A_2>) AS '種別A',
        (<元列B_1>, <元列B_2>) AS '種別B'
    )
);

-- テーブル：四半期別の売上額と件数（横持ち）
CREATE TABLE sales_summary (
    dept_id      NUMBER,
    q1_amount    NUMBER,
    q1_count     NUMBER,
    q2_amount    NUMBER,
    q2_count     NUMBER,
    q3_amount    NUMBER,
    q3_count     NUMBER
);
INSERT INTO sales_summary VALUES (10, 1200000, 45, 1350000, 52, 980000, 38);
INSERT INTO sales_summary VALUES (20,  850000, 30,  920000, 35, 770000, 28);
COMMIT;

-- 売上額（amount）と件数（count）を同時に縦持ち変換
SELECT dept_id, quarter, amount, cnt
FROM sales_summary
UNPIVOT (
    (amount, cnt)
    FOR quarter
    IN (
        (q1_amount, q1_count) AS 'Q1',
        (q2_amount, q2_count) AS 'Q2',
        (q3_amount, q3_count) AS 'Q3'
    )
)
ORDER BY dept_id, quarter;

-- 結果イメージ
-- DEPT_ID  QUARTER  AMOUNT   CNT
-- -------  -------  -------  ---
-- 10       Q1       1200000   45
-- 10       Q2       1350000   52
-- 10       Q3        980000   38
-- 20       Q1        850000   30
-- ...

-- STEP 1: もともとの縦持ちデータ
-- DEPT_ID  MONTH  SALES
-- 10       1      120000
-- 10       2      135000
-- ...

-- STEP 2: PIVOT で月列に展開
WITH pivoted AS (
    SELECT *
    FROM (
        SELECT dept_id, month_no, sales
        FROM sales_raw
    )
    PIVOT (
        SUM(sales)
        FOR month_no IN (1 AS m01, 2 AS m02, 3 AS m03, 4 AS m04,
                         5 AS m05, 6 AS m06, 7 AS m07, 8 AS m08,
                         9 AS m09, 10 AS m10, 11 AS m11, 12 AS m12)
    )
)
-- STEP 3: 再度 UNPIVOT で縦持ちに戻し、追加処理
SELECT dept_id, month_name, sales,
       ROUND(sales / SUM(sales) OVER (PARTITION BY dept_id) * 100, 1) AS share_pct
FROM pivoted
UNPIVOT (
    sales FOR month_name
    IN (m01, m02, m03, m04, m05, m06, m07, m08, m09, m10, m11, m12)
)
ORDER BY dept_id, month_name;

-- 4四半期分を4つの SELECT + UNION ALL で書く必要がある
SELECT dept_id, 'Q1' AS quarter, q1_sales AS sales FROM monthly_sales
UNION ALL
SELECT dept_id, 'Q2' AS quarter, q2_sales AS sales FROM monthly_sales
UNION ALL
SELECT dept_id, 'Q3' AS quarter, q3_sales AS sales FROM monthly_sales
UNION ALL
SELECT dept_id, 'Q4' AS quarter, q4_sales AS sales FROM monthly_sales
ORDER BY dept_id, quarter;
-- 列数が増えるほど SELECT の数が増える（12カ月なら12個の UNION ALL が必要）

-- 1つの SELECT + UNPIVOT 句でまとめて書ける
SELECT dept_id, quarter, sales
FROM monthly_sales
UNPIVOT (
    sales FOR quarter
    IN (q1_sales AS 'Q1', q2_sales AS 'Q2', q3_sales AS 'Q3', q4_sales AS 'Q4')
)
ORDER BY dept_id, quarter;

-- 月別横持ちテーブル（外部から取り込んだデータ想定）
-- PRODUCT_ID  JAN_SALES  FEB_SALES  MAR_SALES  ... DEC_SALES

-- 縦持ちに変換して月ごとの売上推移を分析
SELECT
    product_id,
    month_name,
    sales,
    AVG(sales) OVER (PARTITION BY product_id
                     ORDER BY month_name
                     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3m
FROM product_monthly_sales
UNPIVOT (
    sales FOR month_name
    IN (
        jan_sales AS '01', feb_sales AS '02', mar_sales AS '03',
        apr_sales AS '04', may_sales AS '05', jun_sales AS '06',
        jul_sales AS '07', aug_sales AS '08', sep_sales AS '09',
        oct_sales AS '10', nov_sales AS '11', dec_sales AS '12'
    )
)
ORDER BY product_id, month_name;
-- 縦持ちにした後で分析関数（移動平均）を適用できる

-- アンケートテーブル（横持ち）
-- SURVEY_ID  ANS_Q1  ANS_Q2  ANS_Q3  ANS_Q4  ANS_Q5

-- 縦持ちに変換してから集計（各質問の平均スコアを求める）
SELECT
    question,
    COUNT(*) AS response_count,
    ROUND(AVG(answer_score), 2) AS avg_score,
    MIN(answer_score) AS min_score,
    MAX(answer_score) AS max_score
FROM survey_results
UNPIVOT INCLUDE NULLS (
    answer_score FOR question
    IN (
        ans_q1 AS 'Q1: 総合満足度',
        ans_q2 AS 'Q2: 品質評価',
        ans_q3 AS 'Q3: 価格妥当性',
        ans_q4 AS 'Q4: サポート対応',
        ans_q5 AS 'Q5: 再購入意向'
    )
)
GROUP BY question
ORDER BY question;

-- XMLTABLEを使った動的な縦持ち変換の例
-- （UNPIVOT では列名をコンパイル時に固定する必要があるため、
--   動的に列名を変えたい場合はこのアプローチを使う）

SELECT t.row_num, x.col_name, x.col_value
FROM (
    SELECT rownum AS row_num,
           xmltype(dbms_xmlgen.getxml(
               'SELECT * FROM survey_results WHERE rownum = ' || rownum
           )) AS xml_data
    FROM survey_results
) t,
XMLTABLE('/ROWSET/ROW/*'
    PASSING t.xml_data
    COLUMNS
        col_name  VARCHAR2(100) PATH 'name()',
        col_value VARCHAR2(4000) PATH 'text()'
) x;
-- 注意: 列数が少なく固定できる場合は UNPIVOT の方がシンプルで高速