PIVOT / UNPIVOT 절

PIVOT은 행 값을 열(컬럼)로 펼치고, UNPIVOT은 여러 열을 행으로 접는다. 보고서 형태의 교차표를 만들거나, 반대로 교차표를 정규화된 형태로 되돌릴 때 사용한다.

PIVOT은 QTR 값(Q1, Q2)을 열로 펼치며 집계한다. UNPIVOT으로 접으면 150은 한 행으로 돌아올 뿐 100과 50으로 나뉘지 않는다.

예제 데이터

SALES 테이블

REGION	YR	QTR	AMT
서울	2023	Q1	100
서울	2023	Q2	200
부산	2023	Q1	50
서울	2024	Q1	300
부산	2024	Q2	150

목표는 연도별로 분기 매출을 열로 펼친 교차표다.

YR	Q1	Q2
2023	150	200
2024	300	150

Oracle PIVOT 문법

SELECT *
FROM   (SELECT 컬럼들 FROM 테이블)
PIVOT  (집계함수(값컬럼) [AS 별칭]
        FOR 피벗컬럼 IN (값1 [AS 별칭1], 값2 [AS 별칭2], ...));

집계함수(값컬럼) 새로 생길 각 열에 들어갈 값. SUM, COUNT, MAX 등. 예: SUM(AMT)
FOR 피벗컬럼 값이 열 이름으로 바뀔 컬럼. 예: FOR QTR
IN (값 목록) 열로 만들 값을 명시한다. 목록에 없는 값은 결과에서 빠진다. 예: IN ('Q1', 'Q2')

SELECT *
FROM   (SELECT YR, QTR, AMT FROM SALES)
PIVOT  (SUM(AMT) FOR QTR IN ('Q1' AS Q1, 'Q2' AS Q2))
ORDER  BY YR;

YR	Q1	Q2
2023	150	200
2024	300	150

IN 절의 값은 상수로 직접 나열해야 한다. 서브쿼리로 동적 목록을 줄 수 없다(XML 형식 제외).
별칭을 생략하면 열 이름이 값 그대로('Q1'처럼 따옴표 포함) 만들어지므로 별칭을 주는 편이 편리하다.
해당 조합의 데이터가 없으면 NULL이 들어간다.

암시적 GROUP BY 주의

PIVOT은 집계함수와 FOR 절에 쓰이지 않은 나머지 컬럼을 모두 기준으로 자동 GROUP BY를 수행한다. 인라인 뷰 없이 테이블을 그대로 쓰면 원하지 않는 컬럼까지 그룹 기준이 된다.

FROM 결과 컬럼 REGION, YR, QTR, AMT
집계 대상 제외 AMT
FOR 컬럼 제외 QTR
남은 컬럼 = 행 기준 REGION, YR

SELECT *
FROM   SALES
PIVOT  (SUM(AMT) FOR QTR IN ('Q1' AS Q1, 'Q2' AS Q2))
ORDER  BY REGION, YR;

REGION	YR	Q1	Q2
부산	2023	50	NULL
부산	2024	NULL	150
서울	2023	100	200
서울	2024	300	NULL

행이 4개로 늘어난 이유

REGION이 그룹 기준에 포함되어 연도별 2행이 아니라 지역·연도별 4행이 된다. 인라인 뷰에서 필요한 컬럼(YR, QTR, AMT)만 선택해야 의도한 결과를 얻는다.

시험 포인트: PIVOT 결과의 행 기준은 "FROM 절 결과 컬럼 중 집계 대상과 FOR 컬럼을 뺀 나머지 컬럼"이다. 결과 행 수를 묻는 문제는 이 나머지 컬럼의 고유 조합 수를 세면 된다.

여러 집계함수 사용

SELECT *
FROM   (SELECT YR, QTR, AMT FROM SALES)
PIVOT  (SUM(AMT) AS S, COUNT(*) AS C
        FOR QTR IN ('Q1' AS Q1, 'Q2' AS Q2));

YR	Q1_S	Q1_C	Q2_S	Q2_C
2023	150	2	200	1
2024	300	1	150	1

집계함수가 여러 개면 열 이름은 IN 별칭_집계 별칭 형태로 만들어진다.

CASE와 집계함수로 같은 결과 만들기

PIVOT 절을 지원하지 않는 환경이나 구버전에서는 CASE 표현식과 GROUP BY로 같은 결과를 만든다.

SELECT YR,
       SUM(CASE WHEN QTR = 'Q1' THEN AMT END) AS Q1,
       SUM(CASE WHEN QTR = 'Q2' THEN AMT END) AS Q2
FROM   SALES
GROUP  BY YR
ORDER  BY YR;

YR	Q1	Q2
2023	150	200
2024	300	150

CASE 방식의 특징

GROUP BY 기준을 직접 지정하므로 암시적 그룹 문제가 없다.
ELSE 0을 넣으면 데이터가 없는 칸이 NULL 대신 0이 된다.
Oracle에서는 SUM(DECODE(QTR, 'Q1', AMT))처럼 DECODE로도 쓸 수 있다. CASE와 DECODE는 함수를 참고한다.

SQL Server PIVOT

SELECT YR, [Q1], [Q2]
FROM   (SELECT YR, QTR, AMT FROM SALES) AS S
PIVOT  (SUM(AMT) FOR QTR IN ([Q1], [Q2])) AS P;

IN 목록의 값은 대괄호로 감싼 식별자로 쓴다.
원본 집합과 PIVOT 결과에 모두 별칭이 필요하다.
암시적 GROUP BY 동작은 Oracle과 같다.

UNPIVOT 문법

UNPIVOT은 여러 열을 "열 이름 컬럼 + 값 컬럼" 두 컬럼의 여러 행으로 바꾼다.

SELECT *
FROM   테이블
UNPIVOT [INCLUDE NULLS | EXCLUDE NULLS]
        (값컬럼명 FOR 이름컬럼명 IN (열1 [AS 값1], 열2 [AS 값2], ...));

예제 테이블 SALES_Y

YR	Q1	Q2
2023	150	200
2024	300	NULL

SELECT YR, QTR, AMT
FROM   SALES_Y
UNPIVOT (AMT FOR QTR IN (Q1 AS 'Q1', Q2 AS 'Q2'))
ORDER  BY YR, QTR;

YR	QTR	AMT
2023	Q1	150
2023	Q2	200
2024	Q1	300

AMT는 새로 만들어지는 값 컬럼, QTR은 원래 열 이름이 들어갈 이름 컬럼이다.
기본값은 EXCLUDE NULLS이므로 2024년 Q2(NULL)는 결과에서 빠진다.

INCLUDE NULLS

SELECT YR, QTR, AMT
FROM   SALES_Y
UNPIVOT INCLUDE NULLS (AMT FOR QTR IN (Q1 AS 'Q1', Q2 AS 'Q2'))
ORDER  BY YR, QTR;

YR	QTR	AMT
2023	Q1	150
2023	Q2	200
2024	Q1	300
2024	Q2	NULL

시험 포인트: UNPIVOT의 기본 동작은 EXCLUDE NULLS다. NULL 값을 가진 열도 행으로 남기려면 INCLUDE NULLS를 명시한다.

UNPIVOT 대체 방법

SELECT YR, 'Q1' AS QTR, Q1 AS AMT FROM SALES_Y WHERE Q1 IS NOT NULL
UNION ALL
SELECT YR, 'Q2', Q2 FROM SALES_Y WHERE Q2 IS NOT NULL;

열 개수만큼 UNION ALL을 반복하거나, 열 개수만큼 행을 가진 복제용 집합과 카티션 곱 후 CASE로 값을 고르는 방식도 쓸 수 있다.

PIVOT과 UNPIVOT 비교

PIVOT · 행 → 열 집계함수 필수. FOR 다음에는 열로 펼칠 기존 컬럼. 결과 행 수는 대개 줄어들고, 데이터 없는 칸은 NULL.
UNPIVOT · 열 → 행 집계함수를 쓰지 않음. FOR 다음에는 새로 만들 이름 컬럼. 결과 행 수는 대개 늘어나고, 기본 EXCLUDE NULLS.

구분	PIVOT	UNPIVOT
방향	행 → 열	열 → 행
집계함수	필수	사용하지 않음
FOR 다음	열로 펼칠 기존 컬럼	새로 만들 이름 컬럼
결과 행 수	대개 줄어듦	대개 늘어남
NULL 처리	데이터 없는 칸이 NULL	기본 EXCLUDE NULLS

PIVOT은 집계 과정에서 원래 상세 행 정보가 사라지므로, PIVOT 결과를 UNPIVOT 해도 원본 데이터가 그대로 복원되지는 않는다.

시험 포인트: PIVOT 결과를 UNPIVOT 하면 원본이 아니라 집계된 값이 행으로 돌아온다. 예제에서 2023년 Q1은 원래 2행(100, 50)이었지만 150 한 행이 된다.

핵심 요약

PIVOT은 행을 열로, UNPIVOT은 열을 행으로 바꾼다.
PIVOT은 집계함수 FOR 컬럼 IN (값 목록) 형태이며 IN 목록은 상수로 나열한다.
PIVOT은 나머지 컬럼 전체로 암시적 GROUP BY 하므로 인라인 뷰로 컬럼을 제한한다.
CASE(또는 DECODE)와 집계함수, GROUP BY로 PIVOT과 같은 결과를 만들 수 있다.
UNPIVOT은 값컬럼 FOR 이름컬럼 IN (열 목록) 형태이며 기본은 EXCLUDE NULLS다.
SQL Server PIVOT은 대괄호 식별자와 별칭이 필요하다.
PIVOT 후 UNPIVOT은 원본을 완전히 복원하지 못한다.