소스: sql-src/adv_05_pivot/02_unpivot_alt.sql, 03_dialects.sql. 02 는 분기가 컬럼인 넓은 표 sales_wide(emp, q1~q4)를 씁니다. 예제 1 의 결과와 같은 값이고, 빈 칸은 NULL 입니다.
열마다 SELECT 하나를 쓰고 UNION ALL 로 잇습니다. 분기 번호는 상수로 붙입니다.
SELECT emp, 1 AS qtr, q1 AS amt FROM sales_wide
UNION ALL
SELECT emp, 2, q2 FROM sales_wide
UNION ALL
SELECT emp, 3, q3 FROM sales_wide
UNION ALL
SELECT emp, 4, q4 FROM sales_wide
ORDER BY emp, qtr;EMP | QTR | AMT
-------+-----+-----
강민호 | 1 | 100
강민호 | 2 | 120
강민호 | 3 | 90
강민호 | 4 | 150
노지은 | 1 | 80
노지은 | 2 | 110
노지은 | 3 | NULL
노지은 | 4 | 95
문서준 | 1 | 130
문서준 | 2 | NULL
문서준 | 3 | 130
문서준 | 4 | NULL
오하늘 | 1 | NULL
오하늘 | 2 | 140
오하늘 | 3 | NULL
오하늘 | 4 | 85
(16행)4행 × 4열이 16행이 되고 NULL 칸도 행으로 남습니다. 첫 SELECT 의 별칭이 결과 컬럼명이 됩니다.
각 SELECT 에 WHERE 열 IS NOT NULL 을 달면 빈 칸이 행이 되지 않습니다.
SELECT emp, 1 AS qtr, q1 AS amt FROM sales_wide WHERE q1 IS NOT NULL
UNION ALL
SELECT emp, 2, q2 FROM sales_wide WHERE q2 IS NOT NULL
UNION ALL
SELECT emp, 3, q3 FROM sales_wide WHERE q3 IS NOT NULL
UNION ALL
SELECT emp, 4, q4 FROM sales_wide WHERE q4 IS NOT NULL
ORDER BY emp, qtr;EMP | QTR | AMT
-------+-----+----
강민호 | 1 | 100
강민호 | 2 | 120
강민호 | 3 | 90
강민호 | 4 | 150
노지은 | 1 | 80
노지은 | 2 | 110
노지은 | 4 | 95
문서준 | 1 | 130
문서준 | 3 | 130
오하늘 | 2 | 140
오하늘 | 4 | 85
(11행)11행이 남았습니다. 이 결과가 뒤의 UNPIVOT 기본 동작과 같습니다. UNION ALL 은 테이블을 열 개수만큼 읽는 단점이 있습니다.
분기 번호 1~4 목록을 CROSS JOIN 하면 사원마다 4행이 생기고, CASE 가 번호에 맞는 열을 고릅니다.
SELECT
w.emp
, n.qtr
, CASE n.qtr
WHEN 1 THEN w.q1
WHEN 2 THEN w.q2
WHEN 3 THEN w.q3
WHEN 4 THEN w.q4
END AS amt
FROM sales_wide w
CROSS JOIN (SELECT 1 AS qtr UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
ORDER BY w.emp, n.qtr;결과는 변형 1 과 같은 16행입니다(sql 파일에서 확인). sales_wide 를 한 번만 읽고, NULL 을 빼려면 바깥 WHERE amt IS NOT NULL 을 인라인 뷰나 CTE 로 감싸 답니다. 열이 많을수록 UNION ALL 보다 짧습니다.
-- Oracle · Tibero(지원 버전 확인)
SELECT emp, qtr, amt
FROM sales_wide
UNPIVOT (amt FOR qtr IN (q1 AS 1, q2 AS 2, q3 AS 3, q4 AS 4))
ORDER BY emp, qtr;도식(H2 미지원, 실행 결과 아님). 변형 2 와 같은 11행입니다.
EMP | QTR | AMT
-------+-----+----
강민호 | 1 | 100
강민호 | 2 | 120
강민호 | 3 | 90
강민호 | 4 | 150
노지은 | 1 | 80
노지은 | 2 | 110
노지은 | 4 | 95
문서준 | 1 | 130
문서준 | 3 | 130
오하늘 | 2 | 140
오하늘 | 4 | 85UNPIVOT 은 기본이 EXCLUDE NULLS 라 빈 칸을 행으로 만들지 않습니다. UNPIVOT INCLUDE NULLS (...) 로 쓰면 변형 1 과 같은 16행이 됩니다. 이 옵션은 Oracle 에만 있습니다.
MSSQL 은 UNPIVOT (amt FOR qtr IN (q1, q2, q3, q4)) 로 쓰고 NULL 을 늘 뺍니다. AS 1 같은 값 지정이 없어서 qtr 컬럼에는 숫자 대신 열 이름 문자열 q1~q4 가 들어갑니다.
-- MSSQL
SELECT
w.emp
, v.qtr
, v.amt
FROM sales_wide w
CROSS APPLY (VALUES (1, w.q1), (2, w.q2), (3, w.q3), (4, w.q4)) v (qtr, amt)
ORDER BY w.emp, v.qtr;행마다 값 목록을 붙이는 방식이라 결과는 변형 1 과 같은 16행이고 NULL 칸이 남습니다. 빼려면 WHERE v.amt IS NOT NULL 을 붙입니다. 문법만 보이고 H2 에서 실행하지 않습니다.
03 파일에서 Oracle·MySQL·MSSQLServer 모드로 같은 조건부 집계를 돌렸고, q1·q2 두 열 결과는 예제 1 의 앞 두 열과 같습니다. CASE WHEN 은 표준이라 모든 모드에서 결과가 같습니다.
DB 별로 쓰는 다른 형태는 다음과 같습니다.
SUM(DECODE(qtr, 1, amt)) 로 씁니다. 03 파일의 Oracle 모드에서 실행했고 CASE 와 결과가 같습니다.SUM(IF(qtr = 1, amt, NULL)) 로도 씁니다. IF 는 H2 미지원이라 03 파일은 CASE 로 실행했습니다.SUM(IIF(qtr = 1, amt, NULL)) 로 씁니다.DECODE 는 조건이 없을 때 NULL 을 돌려주므로 CASE 의 ELSE 생략과 같은 모양입니다. IF·IIF 는 세 번째 인자가 필수라 NULL 을 직접 적습니다.