PIVOT·UNPIVOT: 행과 열 전환

핵심 요약

집계된 행을 열로 펼치는 PIVOT과 열을 행으로 되돌리는 UNPIVOT의 결과 구조를 익힌다.

학습 목표

• PIVOT의 집계·기준·출력값 역할을 구분한다.
• UNPIVOT의 NULL 포함 여부를 설명한다.

본문

개념 지도

원본 행 단위 확인→집계·열 값 지정→행↔열 변환→NULL·중복 검증

핵심 내용

PIVOT은 행의 분류값을 컬럼으로 바꾸면서 집계한다.

SELECT *
FROM (
  SELECT deptno, job, sal FROM emp
)
PIVOT (
  SUM(sal) FOR job IN (
    'CLERK' AS clerk,
    'MANAGER' AS manager
  )
);

집계식과 FOR 대상이 아닌 나머지 컬럼은 암묵적인 그룹 기준이 된다. 원본 인라인 뷰에 불필요한 컬럼을 넣으면 예상보다 세분된 행이 생길 수 있다.

UNPIVOT은 여러 컬럼을 이름·값 행으로 변환한다.

SELECT *
FROM sales
UNPIVOT INCLUDE NULLS (
  amount FOR quarter IN (q1 AS 'Q1', q2 AS 'Q2')
);

기본 UNPIVOT은 입력값이 NULL인 행을 제외하며 INCLUDE NULLS로 포함할 수 있다.

PIVOT·UNPIVOT 판정 순서

PIVOT 문제는 먼저 집계할 값, 열로 바꿀 구분값, 행으로 남을 그룹 기준을 나눈다. PIVOT (SUM(금액) FOR 분기 IN ('1Q' AS Q1, '2Q' AS Q2))에서 금액은 집계 대상, 분기는 열 전환 기준이며, 입력 집합에 남은 다른 컬럼은 암묵적인 GROUP BY 기준이 된다. 불필요한 컬럼이 입력 집합에 남으면 예상보다 행이 여러 개로 나뉠 수 있으므로 PIVOT 전에 필요한 컬럼만 선택한다.

UNPIVOT (금액 FOR 분기 IN (Q1 AS '1Q', Q2 AS '2Q'))는 여러 열을 이름·값 행으로 되돌린다. 기본 동작에서는 값이 NULL인 행이 제외될 수 있고, 보존해야 한다면 INCLUDE NULLS를 명시한다.

SELECT 상품ID, 분기, 금액
FROM 분기매출
UNPIVOT INCLUDE NULLS (
  금액 FOR 분기 IN (Q1 AS '1Q', Q2 AS '2Q')
);

PIVOT을 지원하지 않는 DBMS에서는 조건부 집계로 같은 결과를 만들 수 있다.

SELECT 상품ID,
       SUM(CASE WHEN 분기 = '1Q' THEN 금액 END) AS Q1,
       SUM(CASE WHEN 분기 = '2Q' THEN 금액 END) AS Q2
FROM 매출
GROUP BY 상품ID;

시험에서는 PIVOT의 FOR가 열로 전환할 값을, IN이 생성할 열 목록을 지정한다는 점과 UNPIVOT이 원본 값을 복원하는 역연산이 아니라 열을 행 구조로 재표현하는 연산이라는 점을 구분한다.

PIVOT 전후의 행 단위

원본이 다음과 같다고 가정한다.

DEPT  QUARTER  AMOUNT
10    Q1       100
10    Q2       150
20    Q1       200

SELECT *
FROM sales
PIVOT (
  SUM(amount)
  FOR quarter IN ('Q1' AS q1, 'Q2' AS q2)
);

DEPT  Q1   Q2
10    100  150
20    200  NULL

PIVOT에 명시하지 않은 Column은 암묵적 GROUP BY 기준이 된다. 원본에 같은 부서·분기 행이 여러 건이면 집계 함수가 필요하며, 원하지 않는 Column이 남으면 그룹이 더 잘게 나뉠 수 있다.

UNPIVOT

SELECT dept, quarter, amount
FROM quarterly_sales
UNPIVOT INCLUDE NULLS (
  amount FOR quarter IN (q1 AS 'Q1', q2 AS 'Q2')
);

기본 UNPIVOT은 NULL 값을 제외할 수 있으므로 INCLUDE NULLS와 EXCLUDE NULLS의 결과 차이를 확인한다.

조건부 집계 대안

고정된 소수 열은 SUM(CASE WHEN quarter='Q1' THEN amount END)로도 표현할 수 있다. Dynamic Column 수가 필요한 보고서는 SQL 구조 자체가 변하므로 Dynamic SQL이나 Application Rendering을 검토한다. PIVOT은 표시 구조를 바꾸는 기능이지 결과 정렬을 보장하지 않으므로 ORDER BY를 별도로 작성한다.

자주 틀리는 부분

• PIVOT은 단순한 행·열 모양 변경만이 아니라 집계를 포함한다.
• IN 목록에 없는 분류값은 정적 PIVOT 컬럼으로 나오지 않는다.
• 인라인 뷰의 남은 컬럼이 암묵적 GROUP BY 기준이 됨을 놓치지 않는다.