[DB]데이터베이스 개념노트 23편 - PIVOT, UNPIVOT

데이터베이스 개념노트 23편 - PIVOT, UNPIVOT

지난 글에서는 부모와 자식 관계를 가진 데이터를 트리 구조로 조회하는 계층형 질의 START WITH, CONNECT BY PRIOR에 대해 정리했습니다.

이번 글에서는 SQLD 보강 주제로 자주 등장하는 PIVOT과 UNPIVOT에 대해 정리해 보겠습니다.

PIVOT과 UNPIVOT은 데이터를 보고서 형태로 바꾸거나, 반대로 보고서처럼 넓게 퍼진 데이터를 다시 행 형태로 바꿀 때 사용합니다. 처음에는 문법이 낯설지만, 핵심은 간단합니다.

PIVOT은 행을 열로 바꾸고, UNPIVOT은 열을 행으로 바꿉니다.

---

1. PIVOT이란?

PIVOT은 행으로 저장된 데이터를 열 형태로 변환하는 SQL 문법입니다.

PIVOT은 특정 컬럼의 값을 새로운 컬럼으로 펼쳐서 집계 결과를 보여주는 기능이다.

예를 들어 월별 매출 데이터가 다음처럼 행으로 저장되어 있다고 해보겠습니다.

상품명	월	매출금액
키보드	1월	30000
키보드	2월	45000
마우스	1월	15000
마우스	2월	25000

이 데이터를 상품명 기준으로 1월, 2월 매출을 열로 펼치면 다음과 같은 형태가 됩니다.

상품명	1월	2월
키보드	30000	45000
마우스	15000	25000

이렇게 행에 있던 월 값을 컬럼으로 바꾸는 것이 PIVOT입니다.

---

2. UNPIVOT이란?

UNPIVOT은 PIVOT과 반대로, 열 형태의 데이터를 행 형태로 변환하는 SQL 문법입니다.

UNPIVOT은 여러 컬럼으로 나누어진 데이터를 하나의 컬럼 값으로 모아 행 형태로 변환하는 기능이다.

예를 들어 다음처럼 월별 매출이 열로 저장되어 있다고 해보겠습니다.

상품명	1월	2월	3월
키보드	30000	45000	50000
마우스	15000	25000	30000

UNPIVOT을 사용하면 다음처럼 월과 매출금액을 행으로 바꿀 수 있습니다.

상품명	월	매출금액
키보드	1월	30000
키보드	2월	45000
키보드	3월	50000
마우스	1월	15000
마우스	2월	25000
마우스	3월	30000

---

3. PIVOT과 UNPIVOT 한 번에 이해하기

구분	방향	목적	예시
PIVOT	행 → 열	보고서 형태로 펼쳐서 보기	월 값을 1월, 2월, 3월 컬럼으로 변환
UNPIVOT	열 → 행	분석하기 좋은 행 형태로 변환	1월, 2월, 3월 컬럼을 월 컬럼의 값으로 변환

정리하면 PIVOT은 가로로 펼치는 느낌이고, UNPIVOT은 세로로 모으는 느낌입니다.

---

4. 예제 테이블

이번 글에서는 다음 월별매출 테이블을 기준으로 예제를 살펴보겠습니다.

CREATE TABLE 월별매출 (
  상품명 VARCHAR2(50),
  월 VARCHAR2(10),
  매출금액 NUMBER
);

상품명	월	매출금액
키보드	1월	30000
키보드	2월	45000
키보드	3월	50000
마우스	1월	15000
마우스	2월	25000
마우스	3월	30000

---

5. PIVOT 기본 문법

Oracle 기준 PIVOT 기본 문법은 다음과 같습니다.

SELECT *
FROM (
  SELECT 기준컬럼,
         피벗대상컬럼,
         집계대상컬럼
  FROM 테이블명
)
PIVOT (
  집계함수(집계대상컬럼)
  FOR 피벗대상컬럼 IN (값1 AS 별칭1, 값2 AS 별칭2, 값3 AS 별칭3)
);

PIVOT 문법에서 중요한 요소는 세 가지입니다.

요소	의미	예시
기준 컬럼	행으로 남을 기준	상품명
피벗 대상 컬럼	열로 바꿀 값이 들어 있는 컬럼	월
집계 대상 컬럼	계산할 값	매출금액

---

6. PIVOT 예제

상품별 월 매출을 가로 형태로 바꿔보겠습니다.

SELECT *
FROM (
  SELECT 상품명,
         월,
         매출금액
  FROM 월별매출
)
PIVOT (
  SUM(매출금액)
  FOR 월 IN ('1월' AS JAN, '2월' AS FEB, '3월' AS MAR)
);

위 SQL은 월 컬럼의 값인 1월, 2월, 3월을 각각 JAN, FEB, MAR 컬럼으로 변환합니다.

상품명	JAN	FEB	MAR
키보드	30000	45000	50000
마우스	15000	25000	30000

---

7. PIVOT에서 집계 함수가 필요한 이유

PIVOT은 단순히 행을 열로 바꾸는 것처럼 보이지만, 실제로는 열로 펼친 뒤 집계를 수행합니다.

예를 들어 키보드의 1월 데이터가 여러 건 있을 수 있습니다.

상품명	월	매출금액
키보드	1월	30000
키보드	1월	20000

이 경우 키보드의 1월 매출을 하나의 컬럼으로 보여주려면 합계, 평균, 개수 같은 집계 기준이 필요합니다. 그래서 PIVOT에는 SUM, COUNT, AVG 같은 집계 함수가 들어갑니다.

---

8. COUNT를 이용한 PIVOT

매출금액 합계가 아니라 상품별 월별 주문 건수를 보고 싶다면 COUNT를 사용할 수 있습니다.

SELECT *
FROM (
  SELECT 상품명,
         월,
         매출금액
  FROM 월별매출
)
PIVOT (
  COUNT(매출금액)
  FOR 월 IN ('1월' AS JAN, '2월' AS FEB, '3월' AS MAR)
);

이 SQL은 상품별로 각 월에 매출 데이터가 몇 건 있는지 보여줍니다.

---

9. PIVOT과 GROUP BY의 관계

PIVOT은 내부적으로 그룹별 집계와 비슷한 역할을 합니다. 상품명별, 월별로 데이터를 묶은 뒤 월 값을 열로 펼치는 방식입니다.

다음 GROUP BY 결과를 생각해 보겠습니다.

SELECT 상품명,
       월,
       SUM(매출금액) AS 매출합계
FROM 월별매출
GROUP BY 상품명, 월;

이 결과는 상품명과 월별 매출 합계를 행 형태로 보여줍니다. PIVOT은 이 중 월 값을 열로 바꿔 보고서 형태로 보여주는 것입니다.

---

10. CASE 표현식으로 PIVOT처럼 만들기

PIVOT 문법을 사용하지 않아도 CASE 표현식과 GROUP BY를 이용해 비슷한 결과를 만들 수 있습니다.

SELECT 상품명,
       SUM(CASE WHEN 월 = '1월' THEN 매출금액 ELSE 0 END) AS JAN,
       SUM(CASE WHEN 월 = '2월' THEN 매출금액 ELSE 0 END) AS FEB,
       SUM(CASE WHEN 월 = '3월' THEN 매출금액 ELSE 0 END) AS MAR
FROM 월별매출
GROUP BY 상품명;

이 방식은 PIVOT과 비슷하게 월별 매출을 컬럼으로 보여줍니다.

SQLD에서는 PIVOT 문법 자체뿐만 아니라, 행을 열로 변환한다는 결과 형태를 이해하는 것이 중요합니다.

---

11. PIVOT에서 IN절의 의미

PIVOT의 IN절에는 열로 만들 값을 직접 지정합니다.

FOR 월 IN ('1월' AS JAN, '2월' AS FEB, '3월' AS MAR)

여기서 1월, 2월, 3월은 원본 데이터의 월 컬럼 값입니다. JAN, FEB, MAR은 결과 컬럼명입니다.

원본 값	결과 컬럼명
1월	JAN
2월	FEB
3월	MAR

Oracle PIVOT에서는 일반적으로 피벗할 값을 IN절에 명시해야 합니다.

---

12. PIVOT 결과에서 NULL이 나오는 경우

특정 상품에 특정 월 데이터가 없으면 PIVOT 결과에는 NULL이 나올 수 있습니다.

예를 들어 마우스의 3월 매출 데이터가 없다면 MAR 컬럼은 NULL로 표시될 수 있습니다.

상품명	JAN	FEB	MAR
마우스	15000	25000	NULL

필요하다면 바깥 SELECT에서 NVL을 사용해 NULL을 0으로 바꿀 수 있습니다.

SELECT 상품명,
       NVL(JAN, 0) AS JAN,
       NVL(FEB, 0) AS FEB,
       NVL(MAR, 0) AS MAR
FROM (
  SELECT *
  FROM (
    SELECT 상품명, 월, 매출금액
    FROM 월별매출
  )
  PIVOT (
    SUM(매출금액)
    FOR 월 IN ('1월' AS JAN, '2월' AS FEB, '3월' AS MAR)
  )
);

---

13. UNPIVOT 기본 문법

UNPIVOT은 열로 나누어진 값을 행으로 바꿀 때 사용합니다.

SELECT *
FROM 테이블명
UNPIVOT (
  값컬럼명
  FOR 구분컬럼명 IN (컬럼1 AS 값1, 컬럼2 AS 값2, 컬럼3 AS 값3)
);

요소	의미	예시
값 컬럼명	열에 있던 실제 값을 담을 컬럼	매출금액
구분 컬럼명	어떤 컬럼에서 온 값인지 표시할 컬럼	월
IN절 컬럼	행으로 바꿀 대상 컬럼	JAN, FEB, MAR

---

14. UNPIVOT 예제 테이블

다음처럼 월별 매출이 컬럼으로 저장된 테이블이 있다고 해보겠습니다.

CREATE TABLE 상품매출요약 (
  상품명 VARCHAR2(50),
  JAN NUMBER,
  FEB NUMBER,
  MAR NUMBER
);

상품명	JAN	FEB	MAR
키보드	30000	45000	50000
마우스	15000	25000	30000

---

15. UNPIVOT 예제

JAN, FEB, MAR 컬럼을 월과 매출금액 행으로 바꿔보겠습니다.

SELECT 상품명,
       월,
       매출금액
FROM 상품매출요약
UNPIVOT (
  매출금액
  FOR 월 IN (JAN AS '1월', FEB AS '2월', MAR AS '3월')
);

결과는 다음처럼 나타납니다.

상품명	월	매출금액
키보드	1월	30000
키보드	2월	45000
키보드	3월	50000
마우스	1월	15000
마우스	2월	25000
마우스	3월	30000

---

16. UNPIVOT과 NULL

UNPIVOT은 기본적으로 NULL 값을 가진 컬럼을 결과에서 제외할 수 있습니다.

예를 들어 MAR 컬럼이 NULL인 경우, 해당 월의 행이 결과에 나타나지 않을 수 있습니다.

NULL 값도 결과에 포함하고 싶다면 INCLUDE NULLS를 사용할 수 있습니다.

SELECT 상품명,
       월,
       매출금액
FROM 상품매출요약
UNPIVOT INCLUDE NULLS (
  매출금액
  FOR 월 IN (JAN AS '1월', FEB AS '2월', MAR AS '3월')
);

반대로 NULL을 제외하는 동작을 명확히 표현하려면 EXCLUDE NULLS를 사용할 수 있습니다.

SELECT 상품명,
       월,
       매출금액
FROM 상품매출요약
UNPIVOT EXCLUDE NULLS (
  매출금액
  FOR 월 IN (JAN AS '1월', FEB AS '2월', MAR AS '3월')
);

---

17. PIVOT과 UNPIVOT 비교

구분	PIVOT	UNPIVOT
변환 방향	행을 열로 변환	열을 행으로 변환
주요 목적	보고서 형태로 보기	분석하기 좋은 정규화된 형태로 바꾸기
집계 함수	필요함	일반적으로 집계 함수 사용하지 않음
예시	월 값을 JAN, FEB, MAR 컬럼으로 변환	JAN, FEB, MAR 컬럼을 월 값으로 변환

---

18. PIVOT을 사용하는 상황

PIVOT은 주로 보고서나 통계 화면을 만들 때 사용합니다.

• 월별 매출 보고서
• 지역별 판매 현황
• 부서별 인원 수 요약
• 카테고리별 주문 건수
• 연도별 실적 비교표

데이터를 사람이 보기 좋은 가로 형태로 바꿀 때 PIVOT이 유용합니다.

---

19. UNPIVOT을 사용하는 상황

UNPIVOT은 넓게 퍼진 컬럼 구조를 분석하기 좋은 행 구조로 바꿀 때 사용합니다.

• 월별 컬럼을 월 행으로 변환
• 설문 항목 컬럼을 문항별 행으로 변환
• 엑셀 형태 데이터를 분석용 구조로 변환
• 보고서용 테이블을 원천 데이터 형태로 변환

분석이나 집계 작업은 보통 행 형태 데이터가 더 다루기 쉽기 때문에 UNPIVOT이 도움이 됩니다.

---

20. PIVOT에서 자주 하는 실수

실수	주의할 점
집계 함수를 빼먹음	PIVOT에는 SUM, COUNT 같은 집계 함수가 필요하다.
IN절에 피벗할 값을 명시하지 않음	Oracle PIVOT에서는 열로 만들 값을 지정해야 한다.
피벗 대상 컬럼과 집계 대상 컬럼을 혼동함	열로 바꿀 컬럼과 계산할 컬럼을 구분해야 한다.
NULL 결과를 0으로 착각함	데이터가 없으면 NULL이 나올 수 있다.
동적 컬럼 생성이 자동으로 된다고 생각함	일반 PIVOT에서는 IN절에 값을 직접 지정한다.

---

21. UNPIVOT에서 자주 하는 실수

실수	주의할 점
값 컬럼과 구분 컬럼을 헷갈림	값 컬럼은 실제 숫자 값, 구분 컬럼은 원래 컬럼명을 나타낸다.
NULL 데이터가 사라지는 것을 모름	필요하면 INCLUDE NULLS를 사용한다.
컬럼 데이터 타입을 고려하지 않음	UNPIVOT 대상 컬럼들은 데이터 타입이 호환되어야 한다.
UNPIVOT 결과 행 수를 과소평가함	컬럼 수만큼 행이 늘어날 수 있다.

---

22. SQLD 관점에서 꼭 기억할 내용

이번 글에서 SQLD 공부를 위해 꼭 기억해야 할 내용은 다음과 같습니다.

• PIVOT은 행을 열로 변환한다.
• UNPIVOT은 열을 행으로 변환한다.
• PIVOT은 보고서 형태의 결과를 만들 때 유용하다.
• UNPIVOT은 넓은 컬럼 구조를 분석하기 좋은 행 구조로 바꿀 때 유용하다.
• PIVOT에는 집계 함수가 필요하다.
• PIVOT의 FOR절은 열로 바꿀 대상 컬럼을 지정한다.
• PIVOT의 IN절은 결과 컬럼으로 만들 값을 지정한다.
• UNPIVOT은 여러 컬럼을 하나의 구분 컬럼과 값 컬럼으로 변환한다.
• UNPIVOT에서 NULL 포함 여부는 INCLUDE NULLS, EXCLUDE NULLS와 관련이 있다.
• PIVOT과 UNPIVOT은 결과 형태가 어떻게 바뀌는지 이해하는 것이 중요하다.

---

23. 핵심 요약

개념	핵심 내용
PIVOT	행 데이터를 열 데이터로 변환한다.
UNPIVOT	열 데이터를 행 데이터로 변환한다.
PIVOT 집계 함수	SUM, COUNT, AVG 등으로 값을 집계한다.
FOR절	열로 바꿀 대상 컬럼을 지정한다.
IN절	결과 컬럼으로 만들 값을 지정한다.
INCLUDE NULLS	UNPIVOT 결과에 NULL 값을 포함한다.
EXCLUDE NULLS	UNPIVOT 결과에서 NULL 값을 제외한다.

---

24. 마무리

이번 글에서는 SQLD 보강 주제인 PIVOT과 UNPIVOT에 대해 정리했습니다.

PIVOT은 행으로 저장된 값을 열로 펼쳐 보고서 형태로 보여주는 기능입니다. 월별 매출, 지역별 판매량, 카테고리별 집계처럼 사람이 보기 좋은 형태의 결과를 만들 때 유용합니다.

UNPIVOT은 반대로 여러 컬럼으로 나누어진 데이터를 행 형태로 바꿉니다. 엑셀처럼 넓게 퍼진 데이터를 SQL 분석에 적합한 구조로 바꿀 때 사용할 수 있습니다.

SQLD에서는 PIVOT과 UNPIVOT의 복잡한 실무 활용보다는, 행과 열이 어떻게 바뀌는지, PIVOT에는 집계 함수가 필요하다는 점, UNPIVOT은 열을 행으로 모은다는 점을 정확히 이해하는 것이 중요합니다.

다음 글에서는 SQLD 보강 주제인 정규 표현식과 REGEXP 함수에 대해 정리해 보겠습니다.