[Oracle] 오라클 열을 행으로 바꾸는 UNPIVOT 사용법 및 설명

오라클 열을 행으로 바꾸는 UNPIVOT 사용법 및 설명

오라클에서 UNPIVOT을 사용하면 가로로 출력되는 여러 속성(COLUMN) 데이터를 세로로 돌려 행(ROW)으로 출력할 수 있습니다.

사원들의 부서번호, 직책, 급여 보기

오라클 사용자라면 누구나 알법한 SCOTT 형님(?)의 EMP 테이블입니다.

SELECT * FROM EMP;

사원정보를 담고 있으며, JOB(직책)과 SAL(급여), 부서번호(DEPTNO)가 실습시 사용될 컬럼입니다.

사원들의 부서번호, 직책, 급여정보를 보도록 하겠습니다.

SELECT ENAME, DEPTNO, JOB, SAL
FROM EMP
ORDER BY ENAME;

평소 사용하던 문법이니 출렬 결과를 예상하셨을 것입니다.

여러 속성들을 세로로 보기

만약 부서번호, 직책, 급여를 속성(COLUMN)으로 두지 않고 행(ROW)으로 출력하려면 어떻게 해야할까요? 세가지 속성이니 세 개의 행으로 나누는 것입니다. 마치 게임에서 캐릭터의 능력치를 보는듯합니다.

UNION ALL을 이용한 열(COLUMN)을 행(ROW)으로 바꾸기

가장 단순한 방법으로는 UNION ALL을 이용하여 속성별로 한 행씩 만들어 합치는 것입니다. 결과는 위와 같습니다.

SELECT
       ENAME,
       'JOB' AS 속성명,
       JOB AS 속성값
FROM EMP
UNION ALL
SELECT
       ENAME,
       'SAL' AS 속성명,
       TO_CHAR(SAL) AS 속성값
FROM EMP
UNION ALL
SELECT
       ENAME,
       'DEPTNO' AS 속성명,
       TO_CHAR(DEPTNO) AS 속성값
FROM EMP
ORDER BY ENAME, 속성명;

UNPIVOT을 이용한 열(COLUMN)을 행(ROW)으로 바꾸기

이번에는 이 글의 최종 목적인 UNPIVOT을 사용하여 바꿔보도록 하겠습니다.

SELECT *
FROM (
    SELECT
           ENAME,
           TO_CHAR(DEPTNO) AS DEPTNO,
           JOB,
           TO_CHAR(SAL) AS SAL
    FROM EMP
)
UNPIVOT (
    속성값 FOR 속성명 IN (JOB, SAL, DEPTNO)
)
ORDER BY ENAME, 속성명;

결과는 앞의 UNION ALL을 이용한것과 같습니다.

UNPIVOT 사용 형식 및 설명

먼저 UNPIVOT을 하고싶은 대상 테이블을 FROM절에 지정합니다. FROM절 다음에 UNPIVOT문을 사용할 수 있습니다. 대상 테이블에서 컬럼(속성)이 UNPIVOT시에는 행 데이터로 바뀝니다. 

어떤 컬럼들을 UNPIVOT할것인지를 IN에 명시합니다. 이때 IN에 명시되는 속성들은 타입이 모두 동일애햐 합니다. 왜냐하면 앞의 UNION ALL 예제에서도 보았듯이, 여러행으로 표현하기 위해서는 모든 값들의 타입이 동일해야 하기 때문입니다. UNION ALL과 동일한 원리입니다.

예제에서는 DEPTNO, JOB, SAL이 이에 해당됩니다. 데이터가 아닌 단순 필드명이었는데, UNPIVOT이 되면 하나의 데이터가 됩니다. 그리고 이 값들을 출력시에 어떤 컬럼명으로 출력할것인지의 별칭을 UNPIVOT의 FOR 다음에 선언합니다.

SELECT *
FROM 
    [UNPIVOT 대상 테이블]
UNPIVOT (
    [기존 데이터를 나타낼 필드명] FOR [기존 컬럼명을 나타낼 컬럼명] IN ([세로로 바꿀 컬럼명들])
)

그리고 DEPTNO, JOB, SAL의 실제 데이터(값)들을 표현할 필드의 별칭을 UNPIVOT의 FOR 앞에 선언합니다. 만약 UNPIVOT할 속성들간에 타입이 다르다면 TO_CHAR등의 함수를 통해 FROM절에서 데이터 타입을 통일해준 상태에서 UNPIVOT을 수행해야 합니다.

연관글

[Oracle] 오라클 행을 열로 바꾸는 PIVOT 사용법 및 설명 및 PIVOT 대체하기