【Oracle】縦持ちデータを横持ちに変換する方法｜PIVOT・CASE+GROUP BY・動的PIVOT・UNPIVOT・LISTAGG まで解説

-- 構文
SELECT *
FROM (
    -- ソースクエリ（変換前の縦持ちデータ）
    SELECT product_name, month, sales_amount
    FROM sales
)
PIVOT (
    SUM(sales_amount)         -- 集計関数（SUM / COUNT / AVG / MAX / MIN）
    FOR month                 -- 横に展開する列
    IN ('1月' AS m01,         -- 展開する値とエイリアス
        '2月' AS m02,
        '3月' AS m03)
);

-- NG: row_id などの一意列がソースにあるとグループ化されない
SELECT * FROM (
    SELECT row_id, product_name, month, sales_amount FROM sales  -- row_id が余分
)
PIVOT (SUM(sales_amount) FOR month IN ('1月', '2月', '3月'));
-- → row_id ごとに 1 行になり、横展開にならない

-- OK: 必要な列だけ指定
SELECT * FROM (
    SELECT product_name, month, sales_amount FROM sales
)
PIVOT (SUM(sales_amount) FOR month IN ('1月', '2月', '3月'));

-- AS で列名を指定
SELECT product_name, jan, feb, mar
FROM (
    SELECT product_name, month_num, sales_amount FROM sales
)
PIVOT (
    SUM(sales_amount)
    FOR month_num
    IN (1 AS jan, 2 AS feb, 3 AS mar,
        4 AS apr, 5 AS may, 6 AS jun,
        7 AS jul, 8 AS aug, 9 AS sep,
       10 AS oct, 11 AS nov, 12 AS dec)
);

-- SUM と COUNT を同時に横展開
SELECT *
FROM (
    SELECT product_name, month, sales_amount FROM sales
)
PIVOT (
    SUM(sales_amount) AS total,
    COUNT(*)          AS cnt
    FOR month
    IN ('1月' AS m01, '2月' AS m02, '3月' AS m03)
);
-- 結果列: M01_TOTAL, M01_CNT, M02_TOTAL, M02_CNT, M03_TOTAL, M03_CNT

SELECT
    product_name,
    SUM(CASE WHEN month = '1月' THEN sales_amount END) AS m01,
    SUM(CASE WHEN month = '2月' THEN sales_amount END) AS m02,
    SUM(CASE WHEN month = '3月' THEN sales_amount END) AS m03
FROM sales
GROUP BY product_name
ORDER BY product_name;

SELECT
    product_name,
    SUM(DECODE(month, '1月', sales_amount)) AS m01,
    SUM(DECODE(month, '2月', sales_amount)) AS m02,
    SUM(DECODE(month, '3月', sales_amount)) AS m03
FROM sales
GROUP BY product_name
ORDER BY product_name;

DECLARE
    v_columns VARCHAR2(4000);
    v_sql     VARCHAR2(4000);
    v_cur     SYS_REFCURSOR;
BEGIN
    -- (1) IN 句に列挙する値を動的に取得
    SELECT LISTAGG('''' || month || ''' AS ' || month, ', ')
           WITHIN GROUP (ORDER BY month)
    INTO v_columns
    FROM (SELECT DISTINCT month FROM sales);
    -- 結果例: '1月' AS 1月, '2月' AS 2月, '3月' AS 3月

    -- (2) PIVOT SQL を動的に組み立て
    v_sql := 'SELECT * FROM ('
          || '  SELECT product_name, month, sales_amount FROM sales'
          || ') PIVOT ('
          || '  SUM(sales_amount) FOR month IN (' || v_columns || ')'
          || ')';

    -- (3) 実行
    DBMS_OUTPUT.PUT_LINE(v_sql);  -- デバッグ用
    OPEN v_cur FOR v_sql;
    -- v_cur を使って結果を処理
END;
/

-- 横持ち → 縦持ちに変換
SELECT product_name, month, sales_amount
FROM sales_wide
UNPIVOT (
    sales_amount               -- 値を格納する列
    FOR month                  -- カテゴリ名を格納する列
    IN (jan AS '1月',          -- 元の列名と変換後の値
        feb AS '2月',
        mar AS '3月')
);

-- デフォルト: NULL の行は除外される
SELECT * FROM sales_wide
UNPIVOT (sales_amount FOR month IN (jan, feb, mar));
-- jan=1000, feb=NULL, mar=1200 → feb の行は出力されない

-- INCLUDE NULLS: NULL の行も含める
SELECT * FROM sales_wide
UNPIVOT INCLUDE NULLS (sales_amount FOR month IN (jan, feb, mar));
-- feb の行も NULL のまま出力される

-- 部門ごとに社員名をカンマ区切りで横展開
SELECT department_id,
       LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS members
FROM employees
GROUP BY department_id;

-- 結果例:
-- dept_id=10  members='King, Smith, Ward'
-- dept_id=20  members='Adams, Ford, Jones'

-- 重複除去（Oracle 19c 以降）
SELECT department_id,
       LISTAGG(DISTINCT job_id, ', ') WITHIN GROUP (ORDER BY job_id) AS jobs
FROM employees
GROUP BY department_id;

SELECT *
FROM (
    SELECT product_name,
           TO_CHAR(order_date, 'YYYY-MM') AS ym,
           amount
    FROM orders
    WHERE order_date >= DATE '2025-01-01'
      AND order_date <  DATE '2026-01-01'
)
PIVOT (
    SUM(amount)
    FOR ym IN (
        '2025-01' AS jan, '2025-02' AS feb, '2025-03' AS mar,
        '2025-04' AS apr, '2025-05' AS may, '2025-06' AS jun,
        '2025-07' AS jul, '2025-08' AS aug, '2025-09' AS sep,
        '2025-10' AS oct, '2025-11' AS nov, '2025-12' AS dec
    )
)
ORDER BY product_name;

SELECT *
FROM (
    SELECT department_id, status FROM employees
)
PIVOT (
    COUNT(*)
    FOR status IN ('ACTIVE' AS active, 'INACTIVE' AS inactive, 'PENDING' AS pending)
);
-- 結果:
-- DEPARTMENT_ID  ACTIVE  INACTIVE  PENDING
-- 10             25      3         2
-- 20             30      5         1

-- 質問ごとに回答選択肢（A/B/C/D）の件数を横展開
SELECT *
FROM (
    SELECT question_id, answer FROM survey_responses
)
PIVOT (
    COUNT(*)
    FOR answer IN ('A' AS choice_a, 'B' AS choice_b,
                   'C' AS choice_c, 'D' AS choice_d)
)
ORDER BY question_id;

-- Excel から取り込んだ横持ちテーブル
-- import_sales (product, jan_sales, feb_sales, mar_sales)

INSERT INTO sales_normalized (product, month, amount)
SELECT product, month, amount
FROM import_sales
UNPIVOT (
    amount FOR month
    IN (jan_sales AS 1, feb_sales AS 2, mar_sales AS 3)
);