[SQLD] PIVOT, UNPIVOT 이란 무엇인가? (+기출예상문제)

안녕하세요 yunamom 입니다. ^^

오늘은 2024년 부터 SQLD 시험 2과목 SQL 활용 에 새롭게 추가된 문제유형 PIVOT, UNPIVOT 에 대해 알아보겠습니다.

 

1. PIVOT

먼저 PIVOT 을 직역하면 중심축, 또는 회전하다, 방향을 전환하다 라는 뜻을 가지고 있습니다.

보통 PIVOT을 설명할 때 "행을 열로 전환해서 데이터를 재구성한다" 라고 합니다.

그렇다면.. 어디가 중심이고, 어떻게 회전하는 걸까요? 행과 열, 즉 가로와 세로는 어떤 걸 의미할까요?

여기서 먼저 알아야 할것은 -> 행(Row) = 가로, 열(Column) = 세로 입니다.

그런데 이상하죠? "행을 열로 바꾼다" 면서 왜 결과는 가로로 펼쳐질까요?

처음 접하면 정말 헷갈릴 수 밖에 없습니다. (제가 그랬습니다. ㅎㅎ)

이제 PIVOT 의 기본 문법과 예제를 살펴보며 천천히 이해해봅시다.

 

PIVOT 기본 문법 (오라클)

SELECT 컬럼목록
FROM (
    SELECT 원본컬럼들
    FROM 테이블명
)
PIVOT (
    집계함수(값컬럼)
    FOR 기준컬럼 IN (
        '값1' AS 새컬럼1,
        '값2' AS 새컬럼2,
        ...
    )
);

- 집계함수: 보통 SUM, MAX, COUNT 등을 사용 (*필수)

- 값컬럼: 실제로 집계할 숫자나 수치 데이터

- 기준컬럼: 피벗 기준이 되는 열 (예: 날짜, 카테고리 등)

- IN절: 어떤 값들을 새로운 열로 바꿀지 지정 '값' AS 새컬럼이름 형식

 

PIVOT 예제

SELECT *
FROM (
    SELECT 품목, 날짜, 가격
    FROM 과일_판매
)
PIVOT (
    MAX(가격)
    FOR 날짜 IN (
        'Y2025_07' AS "202507",
        'Y2025_08' AS "202508",
        'Y2025_09' AS "202509"
    )
);

- 품목별로 월별 최고 가격(MAX)을 가로 방향으로

- 'Y2025_07'이라는 날짜 값을 "202507"이라는 새로운 컬럼명으로 바꿔줌 (오라클 숫자 데이터 " " , MS-SQL 은 [ ] 로 감싸줘야함.)

by yunamom

해당 표를 보면 PIVOT 이후의 데이터가 직관적이고 보기 편하다는것을 단번에 알수 있습니다.

그래서 이런 생각이 들 수도 있어요. "처음부터 이렇게 PIVOT 형태로 테이블을 만들면 더 쉽지 않을까?"

하지만 실제로 대부분의 데이터베이스의 테이블 구조는 PIVOT 전 LONG DATA 설계를 하고 있지요 왜 그럴까요? 

	구분	설명
PIVOT 전	LONG DATA	- **행(Row) 을 쌓아서 표현 - 데이터를 아래↓로 줄줄이 쌓아간다 - 정규화된 구조 (DB 친화적) - 다른 테이블과의 JOIN 연산 가능
PIVOT 후	WIDE DATA	- **열(Column) 을 나열해서 표현 - 항목을 옆→으로 늘려 간다.  - 비정규화 구조 - JOIN 불가능

 

그 이유는 데이터베이스의 목적이 '보기 좋게 정리하는 것'이 아니라, '효율적으로 저장, 처리, 확장하는 것' 이기 때문입니다.

이에 따라 시간순으로 쌓이는 값이나 대규모 인원의 정보는 PIVOT을 활용해 일시적으로 구조를 전환함으로써, 보고서나 시각화에서 가독성을 높이는 데 활용됩니다.

예를 들어, 회사 직원 정보나 학생 성적, 제품 판매 기록처럼 시간이 지날수록 누적되는 데이터는 대부분 LONG 형태로 저장됩니다.

PIVOT 문제를 처음 보면 쿼리문이 길고 복잡해 보여서 생소하고 부담스럽게 느껴질 수 있는데요.PIVOT 을 왜 사용해야하는지 이 개념만 제대로 이해하고 나면, PIVOT 문제를 접할 때 훨씬 더 편하게 접근할 수 있습니다.😊

 

2. UNPIVOT

UNPIVOT 이란 UN + PIVOT = PIVOT 을 해제하다. 즉, 열(Column) 을 행(Row) 으로 되돌리는 작업입니다.

 

UNPIVOT 기본 문법 (오라클)

SELECT 컬럼1, 컬럼2, ...
FROM 테이블명
UNPIVOT (
    값이들어갈컬럼 FOR 기준컬럼 IN (기존의 열 목록)
);

UNPIVOT 예제

SELECT 품목, 날짜, 가격
FROM 과일_가격
UNPIVOT (
    가격 
    FOR 날짜 IN (
        Y2025_07, 
        Y2025_08, 
        Y2025_09
    )
);

by yunamom

그렇다면 어떤 상황에서 UNPIVOT이 필요할까요?

- 엑셀 등에서 피벗된 형태로 받은 데이터를 분석할 때

- 시각화나 보고용으로 만든 데이터를 다시 SQL 분석용으로 되돌릴 때

- 외부 시스템/API에서 WIDE 형태로 데이터를 넘겨주는 경우 

	구분	설명
UNPIVOT 전	WIDE DATA	- **열(Column)**을 나열해서 표현 - 항목을 옆→으로 늘려 간다 - 비정규화 구조 - JOIN, 집계, 조건문 처리에 불리
UNPIVOT 후	LONG DATA	- **행(Row)**을 쌓아서 표현 - 데이터를 아래↓로 줄줄이 쌓아간다 - 정규화된 구조 (DB 친화적) - 다른 테이블과의 JOIN 연산 가능, SQL 분석에 유리

즉, PIVOT된 보기 좋은 형태의 데이터 를 가져왔을 때, 그 데이터를 다시 분석 가능한 원래 형태로 바꾸기 위해 UNPIVOT 을 합니다.

3. MS-SQL PIVOT, UNPIVOT

*오라클 과 MS-SQL 의 PIVOT 차이점 을 알아보기

PIVOT 기본 문법 (MS-SQL)

SELECT 고정컬럼, 값1, 값2, ...
FROM (
    SELECT 고정컬럼, 기준컬럼, 값컬럼
    FROM 테이블명
) AS 소스
PIVOT (
    집계함수(값컬럼)
    FOR 기준컬럼 IN (값1, 값2, ...)
) AS 결과별칭;

UNPIVOT 기본 문법 (MS-SQL)

SELECT 고정컬럼, 기준컬럼, 값컬럼
FROM (
    SELECT 고정컬럼, 컬럼1, 컬럼2, ...
    FROM 테이블명
) AS 소스
UNPIVOT (
    값컬럼 FOR 기준컬럼 IN (컬럼1, 컬럼2, ...)
) AS 결과별칭;

 

Oracle vs MS-SQL 문법 차이점

항목	오라클	MS-SQL
IN 내부 별칭	가능 ('Y2025_07' AS "202507")	불가능 (SELECT 절에서 AS로 따로 지정)
서브쿼리 별칭	생략 가능	필수
PIVOT 절 뒤 별칭	❌불가능 (오류)	필수
PIVOT 절 집계함수	필수	필수
숫자 값 컬럼명 표기 방식	큰따옴표 사용 "202507"	대괄호 사용 [202507]

 

집계 함수 종류 (Oracle & MS-SQL 공통)

함수명	설명	데이터 타입
SUM( )	숫자들의 합계를 계산	숫자형만 가능
AVG( )	평균값을 계산	숫자형만 가능
MAX( )	가장 큰 값을 반환	숫자, 문자형 모두 가능 (문자일 경우 사전순 기준)
MIN( )	가장 작은 값을 반환	숫자, 문자형 모두 가능
COUNT( )	값의 개수를 계산	모든 타입 가능
COUNT(DISTINCT 컬럼)	중복을 제외한 값 개수 계산	모든 타입 가능
LISTAGG( )	문자열 연결 (*Oracle : GROUP BY 전용)	문자형 (PIVOT에는 사용 불가)
STRING_AGG( )	문자열 연결 (*MS-SQL : GROUP BY 전용)	문자형 (PIVOT에는 사용 불가)

4. 예상문제

■ 문제1. 다음 SQL 문장에서 sal 값은 어떤 열로 집계되는가?

SELECT *
         FROM (SELECT TO_CHAR (hiredate, 'YYYY') AS yyyy, 
                      job, 
                      deptno, 
                      sal 
                 FROM emp)
        PIVOT (SUM (sal) 
               FOR deptno IN (10, 20, 30))
ORDER BY 1, 2;

 

1) 전체 2) yyyy 3) yyyy. job 4) yyyy. job, deptno

■ 문제2. 아래 S_ENR 테이블에서 결과(Result) 를 구하는 SQL 문장으로 올바른 것은? (Oracle)

[S_ENR]
STUDENT_ID   COURSE   SEMESTER
------------------------------
S001          MATH      2025-1
S002          ENGLISH   2025-1
S003          MATH      2025-1
S004          SCIENCE   2025-1
S005          ENGLISH   2025-2
S006          ENGLISH   2025-1
S007          SCIENCE   2025-2

 

[결과]

SEMESTER   MATH    ENGLISH    SCIENCE
--------------------------------------
 2025-1     2         2          1

 

① 
SELECT *
  FROM (
    SELECT COURSE, STUDENT_ID
      FROM S_ENR
     WHERE SEMESTER = '2025-1'
  )
PIVOT (
  COUNT(*) FOR COURSE IN 
    ('MATH' AS MATH, 'ENGLISH' AS ENGLISH, 'SCIENCE' AS SCIENCE)
)
ORDER BY 1;

② 
SELECT *
  FROM (
    SELECT SEMESTER, COURSE, STUDENT_ID
      FROM S_ENR
  )
PIVOT (
  COUNT(*) FOR COURSE IN 
    ('MATH' AS MATH, 'ENGLISH' AS ENGLISH, 'SCIENCE' AS SCIENCE)
) AS P;

③ 
SELECT *
  FROM (
    SELECT SEMESTER, COURSE,
      FROM S_ENR
     WHERE SEMESTER = '2025-1'
  )
PIVOT (
  COUNT(*) FOR COURSE IN 
    ("MATH" AS MATH, "ENGLISH" AS ENGLISH, "SCIENCE" AS SCIENCE)
);

④
SELECT *
  FROM (
    SELECT SEMESTER, COURSE
      FROM S_ENR
     WHERE SEMESTER = '2025-1'
  )
PIVOT (
  COUNT(*) FOR COURSE IN 
    ('MATH' AS MATH, 'ENGLISH' AS ENGLISH, 'SCIENCE' AS SCIENCE)
)
ORDER BY 1;

---

[정답]

 

문제 1 :  (3)

해설: 집계 함수에 사용한 sal, FOR 절에 사용한 deptno를 제외한 yyyy. job으로 집계된다.

 

문제 2 :  (4)

해설:

1번 ❌ STUDENT_ID를 포함한 채 PIVOT을 수행했기 때문에,결과는 학생별로 수강한 과목이 가로로 나열되어 출력됨.
(학생 1명당 1행씩 출력되며, 학기 구분은 나타나지 않음)

 

2번 ❌ PIVOT 절 뒤에 AS 별칭 사용 → ORA-00933: SQL command not properly ended

 

3번 ❌ IN 절에 컬럼명을 큰따옴표로 묶음 → ORA-56901: non-constant expression is not allowed

 

4번 ✅ 동일한 결과가 나옴.

COURSE를 기준으로 PIVOT을 적용해 과목명이 열(Column)로 전환되어 해당 과목을 수강한 학생 수가 집계되어 표시
'MATH' AS MATH처럼 별칭을 지정했기 때문에 출력 컬럼명은 따옴표 없이 깔끔하게 나옴