PIVOT과 UNPIVOT
쉽게 말하면
PIVOT은 세로로 길게 쌓인 목록을 가로로 펼친 표로 바꿔요. '부서·직무·급여' 목록을 줄은 직무, 칸은 부서로 바꾼 교차표를 만드는 식이에요. 엑셀의 피벗 테이블과 같아요. UNPIVOT은 반대로 가로 칸들을 세로 목록으로 다시 풀어요.
시험용 정의
PIVOT은 행에 있던 값(부서번호 10, 20, 30)을 열로 돌려 집계한 교차표를 만든다. PIVOT (집계함수 FOR 열 IN (값1, 값2 …)). UNPIVOT은 반대로 여러 열을 행으로 펼친다. UNPIVOT (값열 FOR 이름열 IN (열1, 열2 …)). PIVOT은 집계와 FOR에 쓰지 않은 열로 자동 GROUP BY하므로 인라인 뷰로 열을 정리해서 쓴다.
비유로 이해하기
엑셀의 피벗 테이블이다. 세로로 길게 쌓인 '직무·부서·급여' 목록을, 행은 직무, 열은 부서로 펼친 표로 바꾼다. UNPIVOT은 그 표를 다시 세로 목록으로 푼다.
자세히
먼저 알아 둘 말
| 말 | 뜻 |
|---|---|
| 교차표 | 행 기준과 열 기준이 만나는 칸에 집계값을 넣은 표 |
| 암시적 GROUP BY | GROUP BY를 쓰지 않아도 PIVOT이 알아서 하는 그룹화 |
| SALES | 이 페이지의 연습용 표. 분기(QTR)별 SEOUL·BUSAN 매출. Q3 BUSAN은 NULL |
PIVOT
SELECT * FROM (SELECT job, deptno, sal FROM emp) PIVOT (SUM(sal) FOR deptno IN (10 AS d10, 20 AS d20, 30 AS d30))
- FOR 열(deptno)의 IN 목록 값마다 열이 하나씩 생긴다. 별칭을 주면 그 이름, 안 주면 값 자체(10)가 열 이름이다
- 집계 함수와 FOR 열(sal, deptno)을 뺀 나머지 열(job)로 GROUP BY 된다
- IN 목록에 없는 값(부서 NULL인 OH)은 결과 열에 없다
- 집계를 여러 개 쓰면 열 이름은
값별칭_집계별칭(D10_S) - CASE + GROUP BY로 같은 결과를 만들 수 있다:
SUM(CASE WHEN deptno = 10 THEN sal END) d10 … GROUP BY job
UNPIVOT
SELECT * FROM sales UNPIVOT (amt FOR city IN (seoul, busan))
- IN 목록의 열(SEOUL, BUSAN)이 행으로 풀린다. 원래 열 이름은 FOR 열(CITY)에, 값은 값 열(AMT)에 들어간다
- IN 목록에 없는 열(QTR)은 그대로 남는다
- 기본은 EXCLUDE NULLS: 값이 NULL이면 그 행을 만들지 않는다. INCLUDE NULLS로 바꿀 수 있다
seoul AS 'S'처럼 쓰면 이름 열에 들어갈 값을 바꾼다
시험에 이렇게 나와요
- PIVOT 결과 표 고르기, 암시적 GROUP BY 열
- PIVOT과 같은 결과의 CASE + GROUP BY SQL
- UNPIVOT 결과 행 수 (EXCLUDE NULLS 기본)
- IN 목록 별칭과 결과 열 이름
출제 범위·공식 안내 기반
확인 문제
Q1. SELECT * FROM emp PIVOT (SUM(sal) FOR deptno IN (10, 20))처럼 EMP 전체에 바로 PIVOT을 걸면 왜 행이 거의 줄지 않을까요?
- IN 목록이 짧아서
- 오류가 나기 때문
- PIVOT은 행을 줄이지 않는다
- SUM(sal)·deptno에 쓰지 않은 나머지 열 전부(EMPNO 등)로 GROUP BY 되기 때문
정답 보기
4번 SUM(sal)·deptno에 쓰지 않은 나머지 열 전부(EMPNO 등)로 GROUP BY 되기 때문
PIVOT은 집계 함수와 FOR 열에 쓰지 않은 나머지 모든 열로 암시적 GROUP BY를 한다. EMPNO처럼 행마다 다른 열이 남으면 그룹이 줄지 않는다. 그래서 필요한 열만 고른 인라인 뷰에 PIVOT을 건다.
Q2. 인라인 뷰 (SELECT job, deptno, sal FROM emp)에 PIVOT (SUM(sal) FOR deptno IN (10, 20))을 걸 때 GROUP BY 기준이 되는 열은?
- job
- job과 deptno
- sal
- deptno
정답 보기
1번 job
집계에 쓴 sal과 FOR에 쓴 deptno를 뺀 나머지 job.
Q3. 3행 × 열 2개를 UNPIVOT 했는데 그중 값 하나가 NULL이다. 기본 설정의 결과 행 수는?
- 2
- 3
- 5
- 6
정답 보기
3번 5
6행 중 NULL 1개는 EXCLUDE NULLS로 빠져 5행.
Q4. PIVOT과 같은 결과를 내는 방법으로 알맞은 것은?
- ORDER BY를 쓴다
- 집계 함수 안에 CASE를 넣고 GROUP BY 한다
- 윈도우 함수 LAG를 쓴다
- UNION ALL만 쓴다
정답 보기
2번 집계 함수 안에 CASE를 넣고 GROUP BY 한다
SUM(CASE WHEN deptno = 10 THEN sal END) … GROUP BY job.
Q5. UNPIVOT이 하는 일은?
- 행을 열로 바꾼다
- 중복을 없앤다
- 열을 행으로 바꾼다
- 정렬한다
정답 보기
3번 열을 행으로 바꾼다
PIVOT은 행 값을 열로 펼치고, UNPIVOT은 열들을 행으로 접는다.
Q6. PIVOT 절(집계 함수와 FOR 열)에 쓰지 않은 나머지 열은?
- 오류가 난다
- 암시적으로 그룹 기준이 된다
- 모두 합계된다
- 결과에서 버려진다
정답 보기
2번 암시적으로 그룹 기준이 된다
그래서 필요한 열만 인라인 뷰로 골라 PIVOT하는 경우가 많다.
헷갈리기 쉬운 O·X
UNPIVOT은 기본적으로 값이 NULL인 열도 행으로 만든다.
정답 보기
X 기본은 EXCLUDE NULLS. INCLUDE NULLS를 써야 만든다.
PIVOT은 IN 목록에 적은 값만 열로 만든다.
정답 보기
O 목록에 없는 값은 결과에 나타나지 않는다.
PIVOT을 쓰려면 반드시 GROUP BY 절을 함께 써야 한다.
정답 보기
X PIVOT이 암시적으로 그룹화한다.