PIVOT과 UNPIVOT — 행을 열로, 열을 행으로 · SQLD

PIVOT은 한 열의 값들을 열 이름으로 세워 세로로 긴 결과를 가로로 넓은 표로 바꿉니다. UNPIVOT은 반대로 열들을 행으로 돌려세웁니다.

부서별 월급 합계를 GROUP BY로 구하면 부서마다 한 줄씩, 세로로 긴 결과가 나옵니다. 보고서에서는 부서를 가로로 늘어놓고 한 줄에서 견주는 표가 더 읽기 좋을 때가 많습니다. 행을 열로 바꾸는 일을 피벗(PIVOT), 거꾸로 열을 행으로 바꾸는 일을 언피벗(UNPIVOT)이라 합니다. 아래 그림의 재료는 EMP의 DEPTNO와 SAL 두 열, 6행입니다.

SELECT *FROM (  SELECT DEPTNO,    SAL  FROM EMP)PIVOT (  SUM(SAL) FOR DEPTNO  IN (10 AS D10, 20 AS D20,    30 AS D30, 40 AS D40));

SELECT *FROM (  SELECT DEPTNO,    SAL  FROM EMP)PIVOT (  SUM(SAL) FOR DEPTNO  IN (10 AS D10, 20 AS D20,    30 AS D30, 40 AS D40));

SELECT *FROM (  SELECT DEPTNO,    SAL  FROM EMP)PIVOT (  SUM(SAL) FOR DEPTNO  IN (10 AS D10, 20 AS D20,    30 AS D30, 40 AS D40));

SELECT *FROM (  SELECT DEPTNO,    SAL  FROM EMP)PIVOT (  SUM(SAL) FOR DEPTNO  IN (10 AS D10, 20 AS D20,    30 AS D30, 40 AS D40));

SELECT *FROM (  SELECT DEPTNO,    SAL  FROM EMP)PIVOT (  SUM(SAL) FOR DEPTNO  IN (10 AS D10, 20 AS D20,    30 AS D30, 40 AS D40));

SELECT *FROM (  SELECT DEPTNO,    SAL  FROM EMP)PIVOT (  SUM(SAL) FOR DEPTNO  IN (10 AS D10, 20 AS D20,    30 AS D30, 40 AS D40));

SELECT *FROM (  SELECT DEPTNO,    SAL  FROM EMP)PIVOT (  SUM(SAL) FOR DEPTNO  IN (10 AS D10, 20 AS D20,    30 AS D30, 40 AS D40));

…  SELECT ENAME,…ORDER BY ENAME;

SELECT *FROM DEPT_SALUNPIVOT (  SAL FOR DEPTNO  IN (D10 AS 10, D20 AS 20,    D30 AS 30, D40 AS 40))ORDER BY DEPTNO;

SELECT *FROM DEPT_SALUNPIVOT (  SAL FOR DEPTNO  IN (D10 AS 10, D20 AS 20,    D30 AS 30, D40 AS 40))ORDER BY DEPTNO;

SELECT *FROM DEPT_SALUNPIVOT (  SAL FOR DEPTNO  IN (D10 AS 10, D20 AS 20,    D30 AS 30, D40 AS 40))ORDER BY DEPTNO;

SELECT *FROM DEPT_SALUNPIVOT INCLUDE NULLS (  SAL FOR DEPTNO  IN (D10 AS 10, D20 AS 20,    D30 AS 30, D40 AS 40))ORDER BY DEPTNO;

SELECT *FROM DEPT_SALUNPIVOT (  SAL FOR DEPTNO  IN (D10 AS 10, D20 AS 20,    D30 AS 30, D40 AS 40))ORDER BY DEPTNO;

SELECT *FROM DEPT_SALUNPIVOT (  SAL FOR DEPTNO  IN (D10 AS 10, D20 AS 20,    D30 AS 30, D40 AS 40))ORDER BY DEPTNO;

SELECT *FROM (  SELECT DEPTNO,    SAL  FROM EMP)PIVOT (  SUM(SAL) FOR DEPTNO  IN (10 AS D10, 20 AS D20,    30 AS D30, 40 AS D40));

SELECT *FROM (  SELECT DEPTNO,    SAL  FROM EMP)PIVOT (  SUM(SAL) FOR DEPTNO  IN (10 AS D10, 20 AS D20,    30 AS D30, 40 AS D40));

EMP

DEPTNOSAL

10520

20380

20410

30450

NULL300

20470

결과

7단계 중 1단계. PIVOT은 한 열의 값들을 열 이름으로 세워 세로로 긴 표를 가로로 넓은 표로 바꿉니다.

1PIVOT — 행을 열로

PIVOT 절은 집계 함수, FOR 뒤의 열, IN 목록 세 부분으로 됩니다.

PIVOT (
  SUM(SAL) FOR DEPTNO
  IN (10 AS D10, 20 AS D20,
    30 AS D30, 40 AS D40)
)

DEPTNO 값 가운데 IN 목록에 적은 값만 열이 됩니다. 재료에 없는 40도 열은 생기고, 목록에 없는 정하늘의 NULL은 어느 열에도 들지 못해 SAL 300이 결과 어디에도 없습니다. 들어온 행이 없는 칸에는 집계 함수가 빈 그룹에 내는 값이 들어가, SUM이면 NULL입니다. 집계 함수가 없으면 ORA-56902 오류이고, IN 목록에는 서브쿼리가 아닌 상수 값을 적습니다. AS D10 같은 별칭을 빼면 열 이름이 10이 되어 쓸 때마다 "10"처럼 큰따옴표로 감싸야 합니다. 집계 함수에도 SUM(SAL) AS S처럼 별칭을 붙이면 열 이름이 값 별칭과 밑줄로 이어진 D10_S가 됩니다.

PIVOT은 GROUP BY 없이도 PIVOT 절에 쓰지 않은 열로 묶습니다. 재료에 ENAME을 더하면 사원마다 한 줄씩 6줄로 쪼개지고, 정하늘 줄은 값 칸이 모두 NULL로 남습니다. 그래서 재료는 인라인 뷰로 쓸 열만 추려 넘깁니다. PIVOT 절이 없던 때부터 쓰던 CASE·DECODE와 집계 함수로도 같은 표를 만듭니다.

할 일	PIVOT	CASE · DECODE
값마다 열	IN 목록에 적음	열마다 식 하나
값 모으기	집계 함수 하나	식마다 SUM 등
묶는 기준	쓰지 않은 열 모두	GROUP BY에 적은 열

2UNPIVOT — 열을 행으로

UNPIVOT 괄호의 SAL FOR DEPTNO와 D10 AS 10은 함께 'D10 열의 값은 SAL 열로, 열 이름 D10은 DEPTNO 열의 값 10으로'라는 뜻입니다. 별칭을 빼면 DEPTNO에 'D10' 같은 문자 값이 내려옵니다. 기본값 EXCLUDE NULLS는 값이 NULL인 칸을 행으로 내리지 않습니다. D40을 40, NULL 행으로 남기려면 INCLUDE NULLS를 적습니다. SQL Server의 UNPIVOT도 NULL 칸을 뺍니다.

쓴 말	D40(NULL) 칸	결과
없음 · EXCLUDE NULLS	뺌	3행
INCLUDE NULLS	남김	4행

3되돌릴 수 없는 까닭

UNPIVOT 결과 3행은 처음 재료 6행과 다릅니다. 20번의 380 · 410 · 470은 PIVOT의 SUM이 1260 한 값으로 합쳤고, 정하늘의 300은 처음부터 어느 열에도 들지 못했습니다. 합친 값에는 무엇을 몇 개 합쳤는지 남지 않아, UNPIVOT은 열을 행으로 돌릴 뿐 PIVOT 전으로 되돌리지 못합니다.

예제

그림의 PIVOT입니다.

SELECT *
FROM (
  SELECT DEPTNO,
    SAL
  FROM EMP
)
PIVOT (
  SUM(SAL) FOR DEPTNO
  IN (10 AS D10, 20 AS D20,
    30 AS D30, 40 AS D40)
);

D10	D20	D30	D40
520	1260	450	NULL

같은 결과를 CASE로 씁니다. SUM(DECODE(DEPTNO, 10, SAL))⁠처럼 DECODE로 써도 같습니다.

SELECT SUM(CASE
    WHEN DEPTNO = 10 THEN SAL
  END) AS D10,
  SUM(CASE
    WHEN DEPTNO = 20 THEN SAL
  END) AS D20,
  SUM(CASE
    WHEN DEPTNO = 30 THEN SAL
  END) AS D30,
  SUM(CASE
    WHEN DEPTNO = 40 THEN SAL
  END) AS D40
FROM EMP;

D10	D20	D30	D40
520	1260	450	NULL

PIVOT 결과를 CREATE TABLE … AS SELECT(쿼리 결과로 새 테이블을 만드는 문장 — DDL 레슨에서 자세히)로 DEPT_SAL에 담아 두고 UNPIVOT합니다.

CREATE TABLE DEPT_SAL AS
SELECT *
FROM (
  SELECT DEPTNO,
    SAL
  FROM EMP
)
PIVOT (
  SUM(SAL) FOR DEPTNO
  IN (10 AS D10, 20 AS D20,
    30 AS D30, 40 AS D40)
);

SELECT *
FROM DEPT_SAL
UNPIVOT (
  SAL FOR DEPTNO
  IN (D10 AS 10, D20 AS 20,
    D30 AS 30, D40 AS 40)
)
ORDER BY DEPTNO;

DEPTNO	SAL
10	520
20	1260
30	450

SQL Server도 PIVOT·UNPIVOT을 씁니다. 재료 쿼리와 PIVOT 결과에 별칭(아래의 S와 P)을 붙이고, IN 목록의 값은 [10]처럼 대괄호로 감쌉니다.

SELECT [10] AS D10,
  [20] AS D20,
  [30] AS D30,
  [40] AS D40
FROM (
  SELECT DEPTNO,
    SAL
  FROM EMP
) AS S
PIVOT (
  SUM(SAL) FOR DEPTNO
  IN ([10], [20], [30], [40])
) AS P;

시험 포인트

PIVOT은 집계 함수(값 열) FOR 열 IN (값 목록) 꼴이고, 집계 함수가 꼭 있어야 합니다.
목록에 없는 값은 어느 열에도 들지 못하고, 데이터가 없는 칸에는 집계 함수가 빈 그룹에 내는 값이 들어갑니다.
테이블에 바로 PIVOT을 걸면 남은 열이 모두 묶는 기준이 되어 줄이 쪼개집니다.
UNPIVOT의 기본은 EXCLUDE NULLS라서 NULL 칸은 행이 되지 않습니다.
PIVOT한 결과를 UNPIVOT해도 처음 재료로 돌아오지 않습니다.
PIVOT은 CASE·DECODE와 집계 함수로 바꿔 쓸 수 있습니다.

확인 문제

1. 다음 쿼리의 결과에서 D40은 NULL일까요, 0일까요? 까닭도 말해 보세요.

SELECT *
FROM (
  SELECT DEPTNO
  FROM EMP
)
PIVOT (
  COUNT(*) FOR DEPTNO
  IN (10 AS D10, 20 AS D20,
    30 AS D30, 40 AS D40)
);

정답

0입니다.

D10	D20	D30	D40
1	3	1	0

D40 칸에 들어온 행이 없어도 COUNT는 센 행 수 0을 냅니다. 빈 그룹에서 COUNT는 0, SUM은 NULL인 집계 함수 규칙(GROUP BY와 집계 함수)이 PIVOT의 칸마다 그대로 걸립니다.

2. 인라인 뷰 없이 EMP에 바로 PIVOT을 걸었습니다. 결과는 몇 줄이고, 어떤 열이 생길까요?

SELECT *
FROM EMP
PIVOT (
  SUM(SAL) FOR DEPTNO
  IN (10 AS D10, 20 AS D20,
    30 AS D30, 40 AS D40)
)
ORDER BY EMPNO;

정답

6줄입니다. 열은 EMPNO · ENAME · MGR과 D10 · D20 · D30 · D40입니다.

EMPNO	ENAME	MGR	D10	D20	D30	D40
1001	김민준	NULL	520	NULL	NULL	NULL
1002	이서연	1006	NULL	380	NULL	NULL
1003	박지호	1006	NULL	410	NULL	NULL
1004	최유나	1001	NULL	NULL	450	NULL
1005	정하늘	1004	NULL	NULL	NULL	NULL
1006	강도윤	1001	NULL	470	NULL	NULL

SAL과 DEPTNO 말고 남은 EMPNO · ENAME · MGR이 모두 묶는 기준이 됩니다. EMPNO가 행마다 달라 한 줄도 합쳐지지 않습니다.

3. 1번 결과를 DEPT_CNT 테이블로 만들어 UNPIVOT하면 몇 행이 나올까요?

CREATE TABLE DEPT_CNT AS
SELECT *
FROM (
  SELECT DEPTNO
  FROM EMP
)
PIVOT (
  COUNT(*) FOR DEPTNO
  IN (10 AS D10, 20 AS D20,
    30 AS D30, 40 AS D40)
);

SELECT *
FROM DEPT_CNT
UNPIVOT (
  CNT FOR DEPTNO
  IN (D10 AS 10, D20 AS 20,
    D30 AS 30, D40 AS 40)
)
ORDER BY DEPTNO;

정답

4행입니다.

DEPTNO	CNT
10	1
20	3
30	1
40	0

UNPIVOT이 기본으로 빼는 것은 값이 NULL인 칸뿐입니다. D40은 0이라 40, 0 행으로 남습니다.