SQL활용 - PIVOT 절과 UNPIVOT절 사용법 핵심 정리

 

SQL활용 - PIVOT 절과 UNPIVOT절 사용법 핵심 정리

 

PIVOT은 회전시킨다는 뜻입니다.  데이터베이스의 PIVOT 절은 기존 테이블의 행을 열로 회전시키고 UNPIVOT 절은 열을 행으로 회전시킵니다.

PIVOT 절

- AGGREGATE_FUNCTION : 집계할 열을 지정합니다.

- FOR : PIVOT 할 열을 지정합니다.

- IN : PIVOT 할 열 값을 지정합니다.

 

 PIVOT (
   AGGREGATE_FUNCTION (EXPR) [[AS] ALIAS]
   FOR {COLUMN | (COLUMN [, COLUMN]...)}
   IN ({{{EXPR | (EXPR [,EXPR]...)} [[AS] ALIAS]}... | SUBQUERY | ANY [,ANY]...})
 )

 

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

 

SELECT *
FROM (
    SELECT JOB_ID, DEPARTMENT_ID, SALARY FROM HR.EMPLOYEES
)
PIVOT (
  SUM(SALARY)
  FOR DEPARTMENT_ID 
  IN (90,60,100)
)

 

아래는 인라인 뷰에 yyyy 표현식을 추가한 쿼리입니다. 행 그룹에 yyyy 표현식이 추가된 것을 확인할 수 있습니다.

 

SELECT *
FROM (
    SELECT
    TO_CHAR(HIRE_DATE, 'YYYY') AS YYYY, JOB_ID, DEPARTMENT_ID, SALARY
    FROM HR.EMPLOYEES
)
PIVOT (
    SUM(SALARY)
    FOR DEPARTMENT_ID
    IN (90,60, 100)
)

 

다음 쿼리는 집계함수와 IN 절에 별칭을 지정했습니다.. 별칭을 지정하면 결과 집합의 열 이름이 변경됩니다.

 

SELECT *
FROM (
    SELECT
    JOB_ID, DEPARTMENT_ID, SALARY
    FROM HR.EMPLOYEES
)
PIVOT (
    SUM(SALARY)
    FOR DEPARTMENT_ID
    IN (90 AS D90, 60 AS D60, 100 AS D100)
)

 

집계함수와 IN 절에 지정한 별칭에 따라 아래와 같은 규칙으로 열명이 부여됩니다. 집계함수와 IN 절 모두 별칭을 지정하는 것이 권장됩니다.

 

-- SUM(SALARY)            | 10  | D10
-- SUM(SALARY) AS SAL     | 10_SAL | D10_SAL

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

```sql 

SELECT JOB_ID , D100_SAL
FROM (
    SELECT
    JOB_ID, DEPARTMENT_ID, SALARY
    FROM HR.EMPLOYEES
)
PIVOT(
    SUM(SALARY) AS SAL 
    FOR DEPARTMENT_ID 
    IN (90 AS D90, 60 AS D60, 100 AS D100));

 

 

PIVOT 절은 다수의 집계함수를 지원합니다. 다음 쿼리는 SUM 함수와 COUNT 함수를 함께 사용합니다.

 

SELECT *
FROM (
    SELECT
    JOB_ID, DEPARTMENT_ID, SALARY
    FROM HR.EMPLOYEES
)
PIVOT (
    SUM(SALARY) AS SAL, 
    COUNT(*) AS CNT 
    FOR DEPARTMENT_ID 
    IN (90 AS D90, 60 AS D60, 100 AS D100));

 

FOR 절에도 다수의 열을 기술할 수 있습니다. 다음과 같이 IN 절에 다중 열을 사용해야합니다.

 

SELECT *
FROM (
    SELECT
    TO_CHAR(HIRE_DATE, 'YYYY') AS YYYY, JOB_ID, DEPARTMENT_ID, SALARY
    FROM HR.EMPLOYEES
)
PIVOT (
    SUM(SALARY) AS SAL, COUNT(*) AS CNT
    FOR (DEPARTMENT_ID, JOB_ID) IN ((90,'AD_VP') AS D90A ,(60, 'IT_PROG') AS D60A, (100,'FI_MGR')AS D100A))
    ORDER BY 1;
)

 

PIVOT 절을 사용할 수 없는 경우 집계함수와 CASE 표현식으로 PIVOT을 수행할 수 있습니다.

 

SELECT JOB_ID
, SUM(CASE DEPARTMENT_ID WHEN 90 THEN SALARY END) AS D90_SAL
, SUM(CASE DEPARTMENT_ID WHEN 60 THEN SALARY END) AS D60_SAL
, SUM(CASE DEPARTMENT_ID WHEN 100 THEN SALARY END) AS D100_SAL
FROM HR.EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID

 

UNPIVOT 절

 

UNPIVOT 절은 PIVOT 절과 반대로 동작합니다. 열이 행으로 전환됩니다.

 

- UNPIVOT column : UNPIVOT 값이 들어갈 열을 지정
- FOR : UNPIVOT 된 값을 설명할 값을 들어갈 열을 지정
- IN : UNPIVOT할 열과 설명할 값을 리터럴 값을 지정

 

  UNPIVOT [{INCLUDE | EXCLUDE} NULLS]
  (
          { COLUMN | (COLUMN[, COL]...)}
          FOR { COLUMN | (COLUMN[, COL]...)}
          IN ({ COLUMN | (COLUMN[, COL]...)}[AS {LETERAL|(LITERAL[, LITERAL]...)}]
          [, { COLUMN | (COLUMN[, COL]...)}[AS {LETERAL|(LITERAL[, LITERAL]...)}]]...
  )

 

피벗테이블을 생성한 후 UNPIVOT 합니다.

 

DROP TABLE T1 PURGE;

CREATE TABLE T1 AS
SELECT JOB_ID, D100_SAL, D60_SAL, D100_CNT, D60_CNT
FROM(
    SELECT JOB_ID, DEPARTMENT_ID, SALARY
    FROM  HR.EMPLOYEES
    WHERE JOB_ID IN ('IT_PROG', 'FI_ACCOUNT')
)
PIVOT (SUM(SALARY) AS SAL, COUNT(*) AS CNT FOR DEPARTMENT_ID IN (100 AS D100, 60 AS D60))

-- 생성한 테이블 조회

SELECT * FROM T1 ORDER BY JOB_ID


SELECT JOB_ID, DEPARTMENT_ID, SAL
FROM T1
UNPIVOT(
    SAL 
    FOR DEPARTMENT_ID IN (D100_SAL, D60_SAL))
ORDER BY 1,2;

 

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

 

SELECT JOB_ID, DEPARTMENT_ID, SAL
FROM T1
UNPIVOT(
    SAL FOR DEPARTMENT_ID
    IN (D100_SAL AS 100, D60_SAL AS 60)
)
ORDER BY 1,2;

 

다음과 같이 INCLUDE NULLS 키워드를 기술하면 UNPIVOT 된 열의 값이 NULL인 행도 결과에 포함됩니다.

 

SELECT JOB_ID, DEPARTMENT_ID, SAL
FROM T1
UNPIVOT INCLUDE NULLS (
  SAL FOR DEPARTMENT_ID
  IN (D100_SAL AS 100, D60_SAL AS 60)
)
ORDER BY 1,2;

 

FOR 절에 다수의 열, IN 절에 다수의 별칭을 지정할 수 있습니다.

 

SELECT * FROM T1
UNPIVOT (
    (SAL, CNT) FOR DEPARTMENT_ID
    IN ((D100_SAL, D100_CNT) AS 100, (D60_SAL, D60_CNT)  AS 60)
)
ORDER BY 1,2;

 

UNPIVOT 절을 사용할 수 없는 경우 카티션 곱을 사용해 대체할 수 있습니다. UNPIVOT할 열의 개수만큼 행을 복제하고, CASE 표현식으로 UNPIVOT 할 열을 선택하는 방식입니다.

 

SELECT A.JOB_ID
, CASE B.LV WHEN 1 THEN 100 WHEN 2 THEN 60 END AS DEPTNO
, CASE B.LV WHEN 1 THEN A.D100_SAL WHEN 2 THEN A.D60_SAL END AS SAL
, CASE B.LV WHEN 1 THEN A.D100_CNT WHEN 2 THEN A.D60_CNT END AS CNT
FROM T1 A
, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <=2) B
ORDER BY 1,2;

 

다음 실행 결과에서 강조한 부분이 CASE 표현식으로 선택한 값입니다.

 

SELECT A.JOB_ID, B.LV, A.D100_SAL, A.D60_SAL, A.D100_CNT, A.D60_CNT
FROM T1 A
, (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <=2) B
ORDER BY A.JOB_ID, B.LV;