소스: sql-src/adv_05_pivot/01_conditional_agg.sql. orders 는 사원 4명의 분기별 매출 12행입니다. 문서준은 3분기에 2건이 있고, 노지은은 3분기, 오하늘은 1·3분기 행이 없습니다.
SELECT
emp
, SUM(CASE WHEN qtr = 1 THEN amt END) AS q1
, SUM(CASE WHEN qtr = 2 THEN amt END) AS q2
, SUM(CASE WHEN qtr = 3 THEN amt END) AS q3
, SUM(CASE WHEN qtr = 4 THEN amt END) AS q4
FROM orders
GROUP BY emp
ORDER BY emp;EMP | Q1 | Q2 | Q3 | Q4
-------+------+------+------+-----
강민호 | 100 | 120 | 90 | 150
노지은 | 80 | 110 | NULL | 95
문서준 | 130 | NULL | 130 | NULL
오하늘 | NULL | 140 | NULL | 85
(4행)12행이 사원 4행으로 접혔습니다. 문서준의 3분기 130 은 70 과 60 이 더해진 값입니다. 행이 없는 칸은 NULL 입니다.
SELECT
emp
, SUM(CASE WHEN qtr = 1 THEN amt END) AS q1
, SUM(CASE WHEN qtr = 2 THEN amt END) AS q2
, SUM(CASE WHEN qtr = 3 THEN amt END) AS q3
, SUM(CASE WHEN qtr = 4 THEN amt END) AS q4
, SUM(amt) AS total
FROM orders
GROUP BY emp
ORDER BY emp;EMP | Q1 | Q2 | Q3 | Q4 | TOTAL
-------+------+------+------+------+------
강민호 | 100 | 120 | 90 | 150 | 460
노지은 | 80 | 110 | NULL | 95 | 285
문서준 | 130 | NULL | 130 | NULL | 260
오하늘 | NULL | 140 | NULL | 85 | 225
(4행)합계는 CASE 없이 SUM(amt) 하나면 됩니다. 분기별 SUM 을 더하는 것보다 짧고, NULL 이 섞여도 안전합니다.
SELECT
emp
, COALESCE(SUM(CASE WHEN qtr = 1 THEN amt END), 0) AS q1
, COALESCE(SUM(CASE WHEN qtr = 2 THEN amt END), 0) AS q2
, COALESCE(SUM(CASE WHEN qtr = 3 THEN amt END), 0) AS q3
, COALESCE(SUM(CASE WHEN qtr = 4 THEN amt END), 0) AS q4
FROM orders
GROUP BY emp
ORDER BY emp;EMP | Q1 | Q2 | Q3 | Q4
-------+-----+-----+-----+----
강민호 | 100 | 120 | 90 | 150
노지은 | 80 | 110 | 0 | 95
문서준 | 130 | 0 | 130 | 0
오하늘 | 0 | 140 | 0 | 85
(4행)COALESCE 는 SUM 바깥에 씁니다. 안쪽 CASE ... ELSE 0 END 로 바꿔도 결과가 같지만, 그러면 행 자체가 없는 칸은 여전히 NULL 입니다. 바깥에 두면 두 경우를 모두 채웁니다.
SELECT
emp
, COUNT(CASE WHEN qtr = 1 THEN 1 END) AS q1_cnt
, COUNT(CASE WHEN qtr = 2 THEN 1 END) AS q2_cnt
, COUNT(CASE WHEN qtr = 3 THEN 1 END) AS q3_cnt
, COUNT(CASE WHEN qtr = 4 THEN 1 END) AS q4_cnt
FROM orders
GROUP BY emp
ORDER BY emp;EMP | Q1_CNT | Q2_CNT | Q3_CNT | Q4_CNT
-------+--------+--------+--------+-------
강민호 | 1 | 1 | 1 | 1
노지은 | 1 | 1 | 0 | 1
문서준 | 1 | 0 | 2 | 0
오하늘 | 0 | 1 | 0 | 1
(4행)COUNT 는 NULL 을 세지 않으므로 조건이 맞는 행에서만 1 을 만들면 건수가 됩니다. COUNT 는 행이 없어도 NULL 이 아니라 0 을 주기 때문에 COALESCE 가 필요 없습니다. 문서준의 3분기 2건이 금액 피벗에서는 보이지 않던 정보입니다.
같은 결과를 PIVOT 으로 쓰면 다음과 같습니다. 원본은 필요한 컬럼만 고른 인라인 뷰로 넘깁니다.
-- Oracle · Tibero(지원 버전 확인)
SELECT *
FROM (
SELECT emp, qtr, amt
FROM orders
)
PIVOT (SUM(amt) FOR qtr IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4))
ORDER BY emp;-- MSSQL
SELECT
emp
, [1] AS q1
, [2] AS q2
, [3] AS q3
, [4] AS q4
FROM (
SELECT emp, qtr, amt
FROM orders
) s
PIVOT (SUM(amt) FOR qtr IN ([1], [2], [3], [4])) p
ORDER BY emp;도식(H2 미지원, 실행 결과 아님). 예제 1 과 같은 데이터로 그린 표입니다.
EMP | Q1 | Q2 | Q3 | Q4
-------+------+------+------+-----
강민호 | 100 | 120 | 90 | 150
노지은 | 80 | 110 | NULL | 95
문서준 | 130 | NULL | 130 | NULL
오하늘 | NULL | 140 | NULL | 85Oracle 은 IN 목록에서 1 AS q1 처럼 값 뒤에 새 열 이름을 붙이고, MSSQL 은 값 자체가 열 이름이 되므로 [1] 처럼 대괄호로 감싸 SELECT 에서 별칭을 답니다. 두 DB 모두 조건부 집계와 달리 GROUP BY 를 쓰지 않습니다.