SQL PIVOT 절

상위 문서: SQL

PIVOT / UNPIVOT Clause
행으로 저장된 값을 칼럼으로 돌려 세우거나(PIVOT), 반대로 여러 칼럼을 행으로 풀어 내리는(UNPIVOT) FROM 절의 기능

PIVOT은 세로로 쌓인 데이터를 가로 형태의 교차표(크로스탭)로 바꾸고, UNPIVOT은 가로로 펼친 칼럼들을 다시 세로 행으로 바꾼다. 예를 들어 지역·분기·금액이 행 단위로 저장된 판매 데이터를 "지역별로 분기마다 한 칼럼"인 표로 만들 때 PIVOT을 쓴다. Oracle은 11g부터, SQL Server는 2005부터 PIVOT·UNPIVOT 절을 지원한다. PIVOT이 없는 DBMS에서는 CASE(Oracle은 DECODE도 가능)와 GROUP BY로 같은 결과를 만든다.

예제 테이블

판매

판매번호	지역	분기	금액
1	서울	Q1	100
2	서울	Q1	50
3	서울	Q2	200
4	부산	Q1	80
5	부산	Q3	120

PIVOT 문법 (Oracle)

SELECT *
FROM (SELECT 지역, 분기, 금액 FROM 판매)          -- 필요한 칼럼만 남긴 인라인 뷰
PIVOT (SUM(금액)                                 -- 집계 함수
       FOR 분기                                  -- 칼럼으로 펼칠 값이 든 칼럼
       IN ('Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3))  -- 펼칠 값과 칼럼 별칭
ORDER BY 지역;

지역	Q1	Q2	Q3
부산	80	NULL	120
서울	150	200	NULL

PIVOT 절은 집계 함수, FOR 절, IN 절로 이루어진다.
FOR 절 칼럼(분기)과 집계 대상 칼럼(금액)을 뺀 나머지 칼럼(지역)이 자동으로 GROUP BY 기준이 된다. 이것을 암묵적 GROUP BY라고 한다.
서울 Q1은 100 + 50 = 150으로 합산된다. 해당 값이 없는 칸(부산 Q2, 서울 Q3)은 NULL이다.
IN 절의 값은 상수로 미리 적어야 한다. 값 목록을 하위 쿼리로 동적으로 정할 수는 없다(XML 형식으로 결과를 내는 PIVOT XML만 예외).
IN 절에 별칭을 주지 않으면 칼럼명이 'Q1', 'Q2'처럼 따옴표가 붙은 이름이 된다.

암묵적 GROUP BY의 함정

인라인 뷰 없이 원본 테이블에 바로 PIVOT을 쓰면, 판매번호까지 GROUP BY 기준에 들어가 행이 전혀 묶이지 않는다.

SELECT *
FROM 판매
PIVOT (SUM(금액) FOR 분기 IN ('Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3))
ORDER BY 판매번호;

판매번호	지역	Q1	Q2	Q3
1	서울	100	NULL	NULL
2	서울	50	NULL	NULL
3	서울	NULL	200	NULL
4	부산	80	NULL	NULL
5	부산	NULL	NULL	120

GROUP BY 판매번호, 지역과 같아져 5행이 그대로 나온다. 지역별 2행을 얻으려면 앞의 예처럼 인라인 뷰로 지역, 분기, 금액만 남겨야 한다.
반대로 인라인 뷰에 분기, 금액만 남기면 남는 칼럼이 없어 전체가 한 그룹이 된다. 결과는 Q1 230, Q2 200, Q3 120인 1행이다.
결과 행 수는 "FOR 칼럼과 집계 대상 칼럼을 뺀 나머지 칼럼"의 서로 다른 값 조합 수다. 시험에서 PIVOT 결과 행 수를 물으면 이것부터 확인한다.

여러 집계 함수

집계 함수를 여러 개 쓰려면 각각 별칭을 붙인다. 결과 칼럼명은 IN 절 별칭_집계 별칭이 된다.

SELECT *
FROM (SELECT 지역, 분기, 금액 FROM 판매)
PIVOT (SUM(금액) AS 합계, COUNT(*) AS 건수
       FOR 분기 IN ('Q1' AS Q1, 'Q2' AS Q2))
ORDER BY 지역;

지역	Q1_합계	Q1_건수	Q2_합계	Q2_건수
부산	80	1	NULL	0
서울	150	2	200	1

해당 값이 없을 때 SUM은 NULL, COUNT는 0이다.

CASE·DECODE와 GROUP BY로 만드는 방법

PIVOT 절이 나오기 전부터 쓰던 방식이며 대부분의 DBMS에서 동작한다. 그룹 기준을 GROUP BY에 직접 적으므로 암묵적 GROUP BY의 함정이 없다.

-- CASE (표준)
SELECT 지역,
       SUM(CASE WHEN 분기 = 'Q1' THEN 금액 END) AS Q1,
       SUM(CASE WHEN 분기 = 'Q2' THEN 금액 END) AS Q2,
       SUM(CASE WHEN 분기 = 'Q3' THEN 금액 END) AS Q3
FROM 판매
GROUP BY 지역
ORDER BY 지역;

-- DECODE (Oracle 전용)
SELECT 지역,
       SUM(DECODE(분기, 'Q1', 금액)) AS Q1,
       SUM(DECODE(분기, 'Q2', 금액)) AS Q2,
       SUM(DECODE(분기, 'Q3', 금액)) AS Q3
FROM 판매
GROUP BY 지역
ORDER BY 지역;

두 쿼리 모두 PIVOT 예제와 같은 결과를 낸다.

지역	Q1	Q2	Q3
부산	80	NULL	120
서울	150	200	NULL

CASE에 ELSE가 없으면 조건에 맞지 않는 행은 NULL이 되고, SUM은 NULL을 무시한다. 그래서 값이 하나도 없는 칸은 NULL이다. 0으로 보이게 하려면 ELSE 0을 쓰거나 NVL로 감싼다.
DECODE(분기, 'Q1', 금액)은 분기가 'Q1'이면 금액, 아니면 NULL을 돌려준다.

UNPIVOT

PIVOT 결과처럼 칼럼으로 펼쳐진 표를 다시 행으로 바꾼다. 아래는 앞의 PIVOT 결과를 분기실적 테이블로 저장한 것이다.

지역	Q1	Q2	Q3
부산	80	NULL	120
서울	150	200	NULL

SELECT *
FROM 분기실적
UNPIVOT (금액                      -- 값이 들어갈 새 칼럼
         FOR 분기                  -- 원래 칼럼명이 들어갈 새 칼럼
         IN (Q1, Q2, Q3))          -- 행으로 풀 칼럼 목록
ORDER BY 지역, 분기;

지역	분기	금액
부산	Q1	80
부산	Q3	120
서울	Q1	150
서울	Q2	200

기본값은 EXCLUDE NULLS다. 값이 NULL인 칸(부산 Q2, 서울 Q3)은 행으로 만들지 않아 4행이 나온다.
UNPIVOT INCLUDE NULLS (금액 FOR 분기 IN (Q1, Q2, Q3))로 쓰면 NULL 칸도 행으로 만들어 2 × 3 = 6행이 나온다. 추가된 2행은 (부산, Q2, NULL), (서울, Q3, NULL)이다.
IN 절에 Q1 AS '1분기'처럼 별칭을 주면 분기 칼럼에 칼럼명 대신 그 값이 들어간다.
UNPIVOT은 PIVOT의 정확한 역연산이 아니다. PIVOT에서 서울 Q1의 100과 50이 150으로 합쳐졌으므로, UNPIVOT으로는 원래의 두 행을 되살릴 수 없다.
UNPIVOT이 없으면 칼럼마다 SELECT를 하나씩 만들어 UNION ALL로 이어 같은 결과를 만들 수 있다.

SQL Server 문법 비교

SQL Server도 PIVOT과 UNPIVOT을 지원하지만 IN 절에 따옴표 문자열이 아닌 대괄호 식별자로 값을 적는다.

-- SQL Server
SELECT 지역, [Q1], [Q2], [Q3]
FROM (SELECT 지역, 분기, 금액 FROM 판매) AS src
PIVOT (SUM(금액) FOR 분기 IN ([Q1], [Q2], [Q3])) AS pvt;

SELECT 지역, 분기, 금액
FROM 분기실적
UNPIVOT (금액 FOR 분기 IN ([Q1], [Q2], [Q3])) AS unpvt;

구분	Oracle	SQL Server
IN 절 값 표기	`'Q1' AS Q1` (문자 상수와 별칭)	`[Q1]` (값 자체를 칼럼 식별자로)
별칭	테이블 별칭 생략 가능	원본 하위 쿼리와 PIVOT 결과에 별칭 필수
집계 함수	여러 개 가능	한 개
UNPIVOT NULL 처리	EXCLUDE NULLS(기본), INCLUDE NULLS 선택	NULL 행은 항상 제외
암묵적 GROUP BY	있음	있음 (인라인 뷰로 칼럼을 제한하는 것도 같다)

시험 포인트

PIVOT의 결과 행 수는 FOR 칼럼과 집계 대상 칼럼을 뺀 나머지 칼럼으로 정해진다. 원본 테이블에 바로 PIVOT하면 불필요한 칼럼까지 그룹 기준이 된다.
PIVOT의 IN 절 값은 미리 적은 상수여야 하며, 별칭이 결과 칼럼명이 된다.
해당 값이 없는 칸은 NULL이다(COUNT는 0).
UNPIVOT은 기본이 EXCLUDE NULLS라 NULL 칸이 행으로 나오지 않는다. INCLUDE NULLS를 쓰면 행 수가 늘어난다.
PIVOT은 CASE 또는 DECODE와 GROUP BY 조합으로 바꿔 쓸 수 있다.

같이 보기

참고 문헌