[Oracle] 컬럼에서 로우로 변경하는 방법. UNPIVOT 을 사용하여 Column To Row

오라클 데이터베이스에서 컬럼을 로우로 변경하고 싶은 경우가 있을 것입니다. 이때 사용할 수 있는 유용한 도구가 언피벗(UNPIVOT) 함수인데요, 사용 방법에 대해 안내해드리도록 하겠습니다.

언피벗이 있으니 피벗(pivot)도 있겠죠? 피벗은 반대로 로우에서 컬럼으로 변경할 수 있는데요, CASE 문이나 DECODE 문 등 대체할 수 있는 함수가 많기 때문에 잘 사용하지 않습니다. 그리하여 언피벗 정도만 알고 가셔도 충분해요!

언피벗 : Column To Row
피벗 : Row To Column

다음은 unpivot을 사용하여 열을 행으로 변환하는 간단한 예제입니다! 😘

  SELECT EMPNO, SAL, COMM
    FROM EMP
ORDER BY EMPNO;

     EMPNO        SAL       COMM
---------- ---------- ----------
      7369        800
      7499       1600        300
      7521       1250        500
      7566       2975
      7654       1250       1400
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500          0
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

위와 같이 [EMPNO], [SAL], [COMM]을 조회해 보았습니다. 이때 EMPNO별로 SAL과 COMM을 컬럼 대신 로우로 조회하고 싶을 때 언피벗을 사용할 수 있습니다.

  SELECT EMPNO, COLUMN_NAME, COLUMN_VALUE
    FROM EMP UNPIVOT (COLUMN_VALUE FOR COLUMN_NAME IN (SAL, COMM))
ORDER BY EMPNO, COLUMN_NAME DESC, COLUMN_VALUE;

     EMPNO COLUMN_NAME COLUMN_VALUE
---------- ----------- ------------
      7369 SAL                  800
      7499 SAL                 1600
      7499 COMM                 300
      7521 SAL                 1250
      7521 COMM                 500
      7566 SAL                 2975
      7654 SAL                 1250
      7654 COMM                1400
      7698 SAL                 2850
      7782 SAL                 2450
      7788 SAL                 3000
      7839 SAL                 5000
      7844 SAL                 1500
      7844 COMM                   0
      7876 SAL                 1100
      7900 SAL                  950
      7902 SAL                 3000
      7934 SAL                 1300

18 rows selected.

'피벗'은 어떤 계산을 하기 위해 선택된 행렬 성분을 뜻하는데요, 테이블 뒤에 언피벗 (컬럼 FOR 컬럼명 IN (언피벗 대상 컬럼 목록)) 과 같은 문법을 가지고 있습니다.

이번 예제에서 사용한 UNPIVOT (COLUMN_VALUE FOR COLUMN_NAME IN (SAL, COMM)) 함수는 SAL, COMM 컬럼에 대해 [COLUMN_VALUE]는 컬럼 값에 대한 앨리어스, [COLUMN_NAME]은 컬럼 이름에 대한 앨리어스라 할 수 있습니다.

언피벗은 기본적으로 NULL 값을 제외하는데요, NULL을 의도적으로 조회하려면 INCLUDE NULLS 지시어를 추가해 주세요. 다음과 같습니다.

  SELECT EMPNO, COLUMN_NAME, COLUMN_VALUE
    FROM EMP UNPIVOT INCLUDE NULLS (COLUMN_VALUE
             FOR COLUMN_NAME
             IN (SAL, COMM)        )
ORDER BY EMPNO, COLUMN_NAME DESC, COLUMN_VALUE;

     EMPNO COLUMN_NAME COLUMN_VALUE
---------- ----------- ------------
      7369 SAL               800
      7369 COMM
      7499 SAL              1600
      7499 COMM              300
      7521 SAL              1250
      7521 COMM              500
      7566 SAL              2975
      7566 COMM
      7654 SAL              1250
      7654 COMM             1400
      7698 SAL              2850
      7698 COMM
      7782 SAL              2450
      7782 COMM
      7788 SAL              3000
      7788 COMM
      7839 SAL              5000
      7839 COMM
      7844 SAL              1500
      7844 COMM                0
      7876 SAL              1100
      7876 COMM
      7900 SAL               950
      7900 COMM
      7902 SAL              3000
      7902 COMM
      7934 SAL              1300
      7934 COMM

28 rows selected.