[Oracle] PIVOT, UNPIVOT 함수 사용 행열 전환 :: 마이자몽

행열 전환

ORACLE SCOTT 계정 EMP 테이블에서 2개의 칼럼을 이용하여 GROUP BY 결과를 확인해봤습니다. 하고 싶은 작업은 GROUPING한 칼럼을 가로 세로축으로 두고 데이터를 출력하려고 합니다. CASE 표현식이나 DECODE 함수를 이용해서 결과를 출력할 수 있습니다.

[Oracle] DECODE 함수 사용 쿼리 결과 가로로 보기 :: 마이자몽

 

DECODE 함수를 사용하면 같은 함수를 계속 사용하면서 중복되는 작업을 해야합니다. 위의 작업을 PIVOT 함수를 이용해서 해결해보겠습니다.

 

 

PIVOT 사용

SELECT
    *
FROM(
    SELECT
        DEPTNO
        ,JOB
        ,SAL
        FROM EMP
) PIVOT(
    AVG(SAL) FOR JOB IN (
        'ANALYST' AS ANALYST
        ,'CLERK' AS CLERK
        ,'MANAGER' AS MANAGER
        ,'PRESIDENT' AS PRESIDENT
        ,'SALESMAN' AS SALESMAN
    )
);

PIVOT함수는 행을 열로 전환해줍니다. 사용할때 테이블을 보고 변환을 하기 때문에 꼭 필요한 칼럼만 사용해야합니다. DEPTNO, JOB, SAL만 조회하도록 했는데 만약 EMPNO처럼 다른 칼럼을 테이블에 넣어버리면 PIVOT할때 같이 그루핑해서 결과를 반환합니다.

 

FOR절 뒤에 상단 칼럼으로 사용할 데이터 칼럼을 위치시키고 출력할 데이터를 IN 함수 안에 넣습니다.

 

 

UNPIVOT 사용

PIVOT 함수가 행을 열로 전환해주는 함수였다면, UNPIVOT은 열을 행으로 전환할 수 있는 함수 입니다. 실습을 진행하기 위해 먼저 CTAS 구문을 사용해서 PIVOT한 테이블을 만들어놓겠습니다.

CREATE TABLE  PIVOT_EMP
AS
SELECT
    *
FROM(
    SELECT
        DEPTNO
        ,JOB
        ,SAL
        FROM EMP
) PIVOT(
    AVG(SAL) FOR JOB IN (
        'ANALYST' AS ANALYST
        ,'CLERK' AS CLERK
        ,'MANAGER' AS MANAGER
        ,'PRESIDENT' AS PRESIDENT
        ,'SALESMAN' AS SALESMAN
    )
);

 

 

UNPIVOT 함수를 사용해서 다시 되돌려보겠습니다.

SELECT
    *
FROM PIVOT_EMP
UNPIVOT (
    SAL FOR JOB IN (
        ANALYST
        ,CLERK
        ,MANAGER
        ,PRESIDENT
        ,SALESMAN
    )
);

UNPIVOT의 사용법도 PIVOT이랑 비슷합니다. 상단에 있던 칼럼명을 IN함수의 인자로 넣어주시면 됩니다.

 

 

PIVOT 사용시 주의사항

DROP TABLE PIVOT_EMP;
CREATE TABLE  PIVOT_EMP
AS
SELECT
    *
FROM(
    SELECT
        DEPTNO
        ,JOB
        ,SAL
        FROM EMP
) PIVOT(
    AVG(SAL) FOR JOB IN ( --PIVOT시 ALIAS를 안
        'ANALYST'
        ,'CLERK'
        ,'MANAGER'
        ,'PRESIDENT'
        ,'SALESMAN'
    )
);

PIVOT을 사용해서 테이블을 따로 만들어 주는 경우라면 IN함수안에 ALIAS를 꼭 넣어서 만들어주는 것이 좋습니다. 위와 같은 테이블을 만들고 조회하면 칼럼의 내용이 아래 처럼 작은 따옴표와 같이 생성이 되버립니다.

조회용으로 사용하는 것 이라면 문제가 없지만, 만약 해당 테이블을 UNPIVOT하게 되면 오류가 발생합니다.

칼럼에 대한 인식은 못합니다.

 

 

SELECT
    *
FROM PIVOT_EMP
UNPIVOT (
    SAL FOR JOB IN (
        "'ANALYST'"
        ,"'CLERK'"
        ,"'MANAGER'"
        ,"'PRESIDENT'"
        ,"'SALESMAN'"
    )
);

큰 따옴표를 이용해서 작은 따옴표까지 칼럼의 일부로 인식하면 UNPIVOT 작업은 문제없이 됩니다. 하지만, 작은 따옴표까지 데이터에 들어가 검색작업에 문제를 일으킬 수 있습니다.