PIVOT / UNPIVOT 완전 가이드

SELECT *
FROM 소스_테이블
PIVOT (
    집계함수(값_컬럼)
    FOR 피벗_컬럼 IN (값1 AS 별칭1, 값2 AS 별칭2, ...)
)

-- 원본 데이터 (long format)
-- | order_date | product_name | amount    |
-- |------------|--------------|-----------|
-- | 2025-01-05 | Laptop       | 3,300,000 |
-- | 2025-01-12 | Mouse        |    25,000 |
-- | 2025-02-03 | Laptop       | 1,500,000 |
-- | 2025-02-18 | Mouse        |    50,000 |

SELECT *
FROM (
    SELECT
        DATE_FORMAT(order_date, 'yyyy-MM') AS month,
        product_name,
        amount
    FROM orders
)
PIVOT (
    SUM(amount)
    FOR product_name IN (
        'Laptop'   AS laptop,
        'Mouse'    AS mouse,
        'Keyboard' AS keyboard,
        'Monitor'  AS monitor
    )
)
ORDER BY month;

-- 매출 합계(total)와 주문 건수(cnt)를 동시에 피벗
SELECT *
FROM (
    SELECT region, product_name, amount
    FROM orders
)
PIVOT (
    SUM(amount) AS total,
    COUNT(*)    AS cnt
    FOR product_name IN ('Laptop' AS laptop, 'Mouse' AS mouse)
);
-- 결과 컬럼: region, laptop_total, laptop_cnt, mouse_total, mouse_cnt

SELECT *
FROM 소스_테이블
UNPIVOT (
    값_컬럼 FOR 카테고리_컬럼 IN (컬럼1, 컬럼2, ...)
)

-- 원본 데이터 (wide format)
-- | year | q1        | q2        | q3        | q4        |
-- |------|-----------|-----------|-----------|-----------|
-- | 2024 | 1,200,000 | 1,500,000 | 1,300,000 | 1,800,000 |
-- | 2025 | 1,400,000 | 1,600,000 | NULL      | NULL      |

SELECT *
FROM quarterly_revenue
UNPIVOT (
    revenue FOR quarter IN (q1, q2, q3, q4)
)
ORDER BY year, quarter;

SELECT *
FROM quarterly_revenue
UNPIVOT INCLUDE NULLS (
    revenue FOR quarter IN (q1, q2, q3, q4)
)
ORDER BY year, quarter;

-- SQL Scripting을 활용한 동적 PIVOT
DECLARE pivot_cols STRING;
DECLARE sql_stmt  STRING;

-- 1) 피벗할 값 목록을 동적으로 수집
SET VAR pivot_cols = (
    SELECT CONCAT_WS(', ',
        COLLECT_LIST(CONCAT("'", product_name, "' AS ", REPLACE(product_name, ' ', '_')))
    )
    FROM (SELECT DISTINCT product_name FROM orders ORDER BY product_name)
);

-- 2) 완성된 PIVOT SQL 조립 후 실행
SET VAR sql_stmt = CONCAT(
    'SELECT * FROM (SELECT DATE_FORMAT(order_date, ''yyyy-MM'') AS month,
      product_name, amount FROM orders)
     PIVOT (SUM(amount) FOR product_name IN (', pivot_cols, '))
     ORDER BY month'
);

EXECUTE IMMEDIATE sql_stmt;

# PySpark로 동적 PIVOT
from pyspark.sql import functions as F

products = [row[0] for row in spark.sql(
    "SELECT DISTINCT product_name FROM orders ORDER BY product_name"
).collect()]

df = spark.sql("""
    SELECT DATE_FORMAT(order_date, 'yyyy-MM') AS month, product_name, amount
    FROM orders
""")

df.groupBy("month").pivot("product_name", products).sum("amount").orderBy("month").display()

-- 지역 × 제품 카테고리 매출 크로스탭
SELECT *
FROM (
    SELECT region, category, revenue
    FROM sales_summary
)
PIVOT (
    SUM(revenue) AS rev,
    COUNT(*)     AS txn
    FOR category IN ('Electronics' AS elec, 'Apparel' AS aprl, 'Food' AS food)
);

SELECT *
FROM (
    SELECT metric_name, report_date, metric_value
    FROM daily_kpi
    WHERE report_date BETWEEN '2025-01-01' AND '2025-01-07'
)
PIVOT (
    AVG(metric_value)
    FOR report_date IN (
        '2025-01-01' AS d0101,
        '2025-01-02' AS d0102,
        '2025-01-03' AS d0103
        -- ...
    )
);

-- 사용자별 이벤트 유형 카운트 → 피처 테이블
SELECT *
FROM (
    SELECT user_id, event_type, 1 AS occurred
    FROM user_events
)
PIVOT (
    COUNT(occurred)
    FOR event_type IN (
        'page_view'    AS f_page_view,
        'add_to_cart'  AS f_add_to_cart,
        'purchase'     AS f_purchase,
        'refund'       AS f_refund
    )
);

-- 파티션 필터를 먼저 적용해 셔플 대상 데이터 최소화
SELECT *
FROM (
    SELECT month, product_name, amount
    FROM orders
    WHERE order_date >= '2025-01-01'  -- 파티션 프루닝 먼저!
)
PIVOT (
    SUM(amount) FOR product_name IN ('Laptop' AS laptop, 'Mouse' AS mouse)
);

SELECT
    DATE_FORMAT(order_date, 'yyyy-MM') AS month,
    SUM(CASE WHEN product_name = 'Laptop'   THEN amount END) AS laptop,
    SUM(CASE WHEN product_name = 'Mouse'    THEN amount END) AS mouse,
    SUM(CASE WHEN product_name = 'Keyboard' THEN amount END) AS keyboard,
    SUM(amount)                                               AS total
FROM orders
GROUP BY 1
ORDER BY month;

SELECT
    month,
    COALESCE(laptop,   0) AS laptop,
    COALESCE(mouse,    0) AS mouse,
    COALESCE(keyboard, 0) AS keyboard
FROM (
    SELECT DATE_FORMAT(order_date, 'yyyy-MM') AS month, product_name, amount
    FROM orders
)
PIVOT (SUM(amount) FOR product_name IN ('Laptop' AS laptop, 'Mouse' AS mouse, 'Keyboard' AS keyboard));

-- 잘못된 예 (집계 함수 없음 → 오류)
PIVOT (amount FOR product_name IN ('Laptop', 'Mouse'))

-- 올바른 예
PIVOT (SUM(amount) FOR product_name IN ('Laptop' AS laptop, 'Mouse' AS mouse))

-- 별칭에 공백/한글 포함 시
PIVOT (SUM(amount) FOR product_name IN ('Laptop Pro' AS `Laptop Pro`, '마우스' AS `마우스`))

-- 이후 참조 시
SELECT `Laptop Pro`, `마우스` FROM pivot_result;

SELECT *
FROM (
    SELECT year,
           CAST(q1 AS BIGINT) AS q1,
           CAST(q2 AS BIGINT) AS q2,
           CAST(q3 AS BIGINT) AS q3
    FROM mixed_type_table
)
UNPIVOT (revenue FOR quarter IN (q1, q2, q3));