[오라클, Oracle] PIVOT 절과 UNPIVOT 절

 

PIVOT은 회전시킨다는 의미를 가지고 있다.

PIVOT 절은 행을 열로 회전시키고, UNPIVOT 절은 열을 행으로 회전시킨다.

PIVOT 절과 UNPIVOT 절은 11.1 버전부터 사용할 수 있다.

 

[PIVOT 절]

PIVOT 절은 행을 열로 전환한다.

 

[기본 문법]

PIVOT 절의 구문은 아래와 같다.

 

PIVOT [XML]

(aggregate_function (expr) [[AS] alias]

[, aggregate_function (expr) [[AS] alias]]...

FOR {column | (column [, column]...)}

IN ({{{expr | (expr [, expr]...)} [[AS] alias]}...

| subquery

| ANY [, ANY] ...

})

)

 

항목 - 설명

aggregate_function - 집계할 열을 지정

FOR 절 - PIVOT할 열을 지정

IN 절 - PIVOT할 열 값을 지정

 

PIVOT 절을 사용한 쿼리.

PIVOT 절은 집계 함수와 FOR 절에 지정되지 않은 열을 기준으로 집계되기 때문에 인라인 뷰를 통해 사용할 열을 지정해야 한다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) FOR DEPTNO IN (10, 20, 30))

ORDER BY 1;

 

아래 쿼리는 인라인 뷰에 YYYY 표현식을 추가했다.

행 그룹에 YYYY 표현식이 추가된 것을 확인할 수 있다.

 

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;

 

아래 쿼리는 집계 함수와 IN 절에 별칭을 지정했다.

별칭을 지정하면 결과 집합의 열명이 변경된다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

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

ORDER BY 1;

 

집계 함수와 IN 절에 지정한 별칭에 따라 아래와 같은 규칙으로 열명이 부여된다.

집계 함수와 IN 절 모두 별칭을 지정하는 편이 바람직하다.

 

10 - 10 AS D10

SUM (SAL) 10 - D10

SUM (SAL) AS SAL 10_SAL - D10_SAL

 

SELECT 절에 부여된 열명을 지정하면 필요한 열만 조회할 수 있다.

 

SELECT JOB, D20_SAL

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

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

WHERE D20_SAL > 2500

ORDER BY 1;

 

PIVOT 절은 다수의 집계 함수를 지원한다.

아래 쿼리는 SUM 함수와 COUNT 함수를 함께 사용했다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) AS SAL, COUNT (*) AS CNT FOR DEPTNO IN (10 AS D10, 20 AS D20))

ORDER BY 1;

 

FOR 절에도 다수의 열을 기술할 수 있다.

아래와 같이 IN 절에 다중 열을 사용해야 한다.

 

SELECT *

FROM (SELECT TO_CHAR (HIREDATE, 'YYYY') AS YYYY, JOB, DEPTNO, SAL FROM EMP)

PIVOT (SUM (SAL) AS SAL, COUNT (*) AS CNT

FOR (DEPTNO, JOB) IN ((10, 'ANALYST') AS D10A, (10, 'CLERK') AS D10C

, (20, 'ANALYST') AS D20A, (20, 'CLERK') AS D20C))

ORDER BY 1;

 

PIVOT 절에 XML 키워드를 기술하면 XML 포맷으로 결과가 반환된다.

XML 키워드를 사용하면 IN 절에 서브 쿼리와 ANY 키워드를 사용할 수 있다.

아래는 서브 쿼리를 사용한 쿼리다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT XML (SUM (SAL) AS SAL FOR DEPTNO IN (SELECT DEPTNO FROM DEPT))

ORDER BY 1;

 

ANY 키워드는 존재하는 값과 일치하는 와일드 카드로 동작한다.

아래 쿼리에서 ANY 키워드는 10, 20, 30과 일치한다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT XML (SUM (SAL) AS SAL FOR DEPTNO IN (ANY))

ORDER BY 1;

 

XML 키워드를 기술해도 다수의 집계 함수와 다수의 열을 사용할 수 있다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL FROM EMP)

PIVOT XML (SUM (SAL) AS SAL, COUNT (*) AS CNT FOR (DEPTNO, JOB) IN (ANY, ANY));

 

[기존 방식]

11.1 이전 버전은 집계 함수와 DECODE 함수로 PIVOT을 수행할 수 있다.

 

SELECT JOB

, SUM (DECODE (DEPTNO, 10, SAL) AS D10_SAL

, SUM (DECODE (DEPTNO, 20, SAL) AS D20_SAL

, SUM (DECODE (DEPTNO, 30, SAL) AS D30_SAL

FROM EMP

GROUP BY JOB

ORDER BY JOB;

 

[UNPIVOT 절]

UNPIVOT 절은 PIVOT 절과 반대로 동작한다.

열을 행으로 전환한다.

 

[기본 문법]

UNPIVOT 절의 구문은 아래와 같다.

 

UNPIVOT [{INCODE | EXCLUDE} NULLS]

( {column | (column [, col]...)}

FOR {column | (column [, col]...)}

IN {column | (column [, col]...)} [AS {literal | (literal [, literal]...)}]

[, {column | (column [, col]...)} [AS {literal | (literal [, literal]...)}]...

)

)

 

항목 - 설명

UNPIVOT column - UNPIVOT된 값이 들어갈 열을 지정

FOR 절 - UNPIVOT된 값을 설명할 값이 들어갈 열을 지정

IN 절 - UNPIVOT할 열과 설명할 값의 리터럴 값을 지정

 

예제를 위해 아래와 같이 테이블 생성

DROP TABLE T1 PURGE;

 

CREATE TABLE T1 AS

SELECT JOB, D10_SAL, D20_SAL, D10_CNT, D20_CNT

FROM (SELECT JOB, DEPTNO, SAL FROM EMP WHERE JOB IN ('ANALYST', 'CLERK'))

PIVOT (SUM (SAL) AS SAL, COUNT (*) AS CNT FOR DEPTNO IN (10 AS D10, 20 AS D20));

 

아래는 T1 테이블을 조회한 결과다.

SELECT * FROM T1 ORDER BY JOB;

 

아래는 UNPIVOT 절을 사용한 쿼리다.

D10_SAL, D20_SAL 열이 행으로 전환된다.

 

SELECT JOB, DEPTNO, SAL

FROM T1

UNPIVOT (SAL FOR DEPTNO IN (D10_SAL, D20_SAL))

ORDER BY 1, 2;

 

IN 절에 별칭을 지정하면 FOR 절에 지정한 열의 값을 변경할 수 있다.

아래 쿼리는 10, 20으로 값을 변경했다.

 

SELECT JOB, DEPTNO, SAL

FROM T1

UNPIVOT (SAL FOR DEPTNO IN (D10_SAL AS 10, D20_SAL AS 20))

ORDER BY 1, 2;

 

아래와 같이 INCLUDE NULLS 키워드를 기술하면 UNPIOT된 열의 값이 널인 행도 결과에 포함된다.

 

SELECT JOB, DEPTNO, SAL

FROM T1

UNPIVOT INCLUDE NULLS (SAL FOR DEPTNO IN (D10_SAL AS 10, D20_SAL AS 20))

ORDER BY 1, 2;

 

UNPIVOT 절에 다수의 열을 지정할 수 있다.

아래 쿼리는 SAL 열과 CNT 열을 UNPIOT 한다.

 

SELECT *

FROM T1

UNPIVOT ((SAL, CNT)

FOR DEPTNO IN ((D10_SAL, D10_CNT) AS 10, (D20_SAL, D20_CNT) AS 20))

ORDER BY 1, 2;

 

FOR 절에도 다수의 열을 지정할 수 있다.

IN 절도 별칭을 함께 지정할 수 있다.

 

SELECT *

FROM T1

UNPIVOT ((SAL, CNT)

FOR (DEPTNO, DNAME) IN ((D10_SAL, D10_CNT) AS (10, 'ACCOUNTING')

, (D20_SAL, D20_CNT) AS (20, 'RESEARCH')))

ORDER BY 1, 2;

 

아래와 같이 PIVOT 절과 UNPIVOT 절을 함께 사용할 수도 있다.

PIVOT 절의 결과가 UNPIVOT 절에 인라인 뷰로 공급되는 방식이다.

INCLUDE NULLS 키워드로 파티션 아우터 조인의 동작을 모방할 수 있다.

 

SELECT *

FROM (SELECT JOB, DEPTNO, SAL, COMM FROM EMP)

PIVOT (SUM (SAL) AS SAL, SUM (COMM) AS COMM

FOR DEPTNO IN (10 AS D10, 20 AS D20, 30 AS D30))

UNPIVOT INCLUDE NULLS

((SAL, COMM) FOR DEPTNO IN ((D10_SAL, D10_COMM) AS 10

, (D20_SAL, D20_COMM) AS 20

, (D30_SAL, D30_COMM) AS 30))

ORDER BY 1, 2;

 

-- 예제를 위해 아래와 같이 테이블을 생성

DROP TABLE T1 PURGE;

 

CREATE TABLE T1 AS

SELECT TO_CHAR (ADD_MONTHS (DATE '2050-01-01', ROWNUM - 1), 'YYYYMM') AS YM

, MOD (ROWNUM, 12) AS V1, MODE (ROWNUM, 12) + 1 AS V2

FROM XMLTABLE ('1 TO 24');

 

-- 아래는 T1 테이블을 조회한 결과다.

SELECT * FROM T1;

 

아래 쿼리는 V1, V2 열을 UNPIVOT 한 후 월별로 V1 열을 PIVOT 한다.

 

SELECT *

FROM (SELECT SUBSTR (YM, 1, 4) AS YY, SUBSTR (YM, 5, 2) AS MM, V1, V2 FROM T1)

UNPIVOT (V1 FOR TP IN (V1, V2))

PIVOT (MAX (VL) AS VL

FOR MM IN ('01' AS M01, '02' AS M02, '03' AS M03

, '04' AS M04, '05' AS M05, '06' AS M06))

ORDER BY 1, 2;

 

[기존 방식]

11.1 이전 버전에서는 카티션 곱을 사용하여 UNPIVOT을 수행할 수 있다.

UNPIVOT할 열의 개수만큼 행을 복제하고 DECODE 함수로 UNPIVOT할 열을 선택하는 방식이다.

 

SELECT A.JOB

, DECODE (B.LV, 1, 10, 2, 20) AS DEPTNO

, DECODE (B.LV, 1, A.D10_SAL, 2, A.D20_SAL) AS SAL

, DECODE (B.LV, 1, A.D10_CNT, 2, A.D20_CNT) AS CNT

FROM T1 A

, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 2) B

ORDER BY 1, 2;

 

아래 쿼리에서 강조된 부분이 DECODE 함수로 선택한 값이다.

SELECT A.JOB, B.LV, A.D10_SAL, A.D20_SAL, A.D10_CNT, A.D20_CNT

FROM T1 A

, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <= 2) B

ORDER BY A.JOB, B.LV;

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.