오라클 PIVOT 동정 컬럼 사용방법(가변IN절)

728x90

오라클에서 PIVOT을 사용하다보면 IN절에 동적으로 값을 할당하고 싶을때가 있다. 그러나 PIVOT의 IN절은 SQL의 IN절과 다르다. PIVOT의 IN절은 할당된 값의 개수와 순서를 맞춰서 결과가 출력된다.

일반적인 쿼리문의 PIVOT은 IN절에 동적으로 값을 할당 할 수 없지만, PIVOT XML 또는 Dynamic SQL을 활용하여 동적 PIVOT을 구현 할 수 있다.

일반적인 PIVOT 쿼리

SELECT *
  FROM (SELECT job
             , deptno
             , sal
          FROM emp
       )
 PIVOT ( SUM(sal) 
         FOR deptno IN ('10', '20', '30', '40') 
       )
 ORDER BY job

PIVOT 쿼리는 IN절 ('10', '20', '30', '40')의 값과 순서에 따라서 결과가 출력된다. '40'이라는 값이 테이블에 존자하지 않아도 결과에 NULL로 출력이 된다.

PIVOT XML을 사용하는 방법

PIVOT XML (IN절에 서브쿼리를 사용가능)

SELECT *
  FROM (SELECT job
             , deptno
             , sal
          FROM emp
       )
 PIVOT XML ( SUM(sal) 
             FOR deptno IN (SELECT deptno FROM dept) 
           )
 ORDER BY job

PIVOT XML은 PIVOT와 다르게 IN절에 서브쿼리를 사용할 수 있다. PIVOT와 다르게 IN절 인자 값(deptno)의 오름차순으로 결과가 출력되며, emp테이블에 해당 값이 없으면 출력되지 않는다.

IN절의 서브쿼리에 파라미터를 사용하여 값을 동적으로 바꿀 수 있다.

ex) deptno IN (SELECT deptno FROM dept WHERE INSTR('파라미터(10,20,30)', deptno) > 0)

PIVOT XML 사용시 결과가 XML로 리턴됨

<PivotSet>
    <item>
        <column name="DEPTNO">10</column>
        <column name="SUM(SAL)" />
    </item>
    <item>
        <column name="DEPTNO">20</column>
        <column name="SUM(SAL)">6000</column>
    </item>
    <item>
        <column name="DEPTNO">30</column>
        <column name="SUM(SAL)" />
    </item>
</PivotSet>

EXTRACTVALUE 함수를 사용하여 XML 값을 순서대로 값을 추출

SELECT job
     , EXTRACTVALUE(deptno_xml, '/PivotSet/item[1]/column[2]') dept1
     , EXTRACTVALUE(deptno_xml, '/PivotSet/item[2]/column[2]') dept2
     , EXTRACTVALUE(deptno_xml, '/PivotSet/item[3]/column[2]') dept3
  FROM (SELECT job
             , deptno
             , sal
          FROM emp
       )
 PIVOT XML ( SUM(sal) 
             FOR deptno IN (SELECT deptno FROM dept) 
           )
 ORDER BY job

PIVOT은 IN절에 할당된 인자 값이 컬럼명으로 출력이 되지만, PIVOT XML은 쿼리문에 작성한 별칭(dept1...)이 출력된다.

Dynamic SQL을 사용하는 방법

Dynamic SQL을 사용하는 Procedure를 생성하여 사용하는 방법

CREATE OR REPLACE PROCEDURE pc_dynamic_pivot(p_cursor in out sys_refcursor)
AS
    sql_param VARCHAR2(1000);
    sql_query VARCHAR2(4000);
BEGIN
    FOR x IN (SELECT deptno FROM dept ORDER BY 1)
    LOOP
       sql_param := sql_param || '''' || x.deptno || '''' || ', ';
    END LOOP;
    
    sql_param := SUBSTR(sql_param, 1, LENGTH(sql_param)-2);

    sql_query := sql_query || ' SELECT *                                     ';
    sql_query := sql_query || '   FROM (SELECT job                           ';
    sql_query := sql_query || '              , deptno                        ';
    sql_query := sql_query || '              , sal                           ';
    sql_query := sql_query || '           FROM emp                           ';
    sql_query := sql_query || '        )                                     ';
    sql_query := sql_query || '  PIVOT ( SUM(sal)                            '; 
    sql_query := sql_query || '          FOR deptno IN (' || sql_param || ') ';
    sql_query := sql_query || '        )                                     ';
    sql_query := sql_query || 'ORDER BY job                                  ';

    open p_cursor for sql_query;
END;

Dynamic SQL을 사용하면 기존 PIVOT와 동일한 결과가 출력되지만 Procedure를 생성해야하는 번거로움이 있다.

사용 예제

-------------------------------------
-- 예제 1
-------------------------------------
WITH emp AS (
    SELECT 7839 empno, 'KING'   ename, 'PRESIDENT' job, NULL mgr, TO_DATE('1981-11-17','yyyy-mm-dd') hiredate, 5000 sal, NULL comm, 10 deptno FROM dual UNION ALL
    SELECT 7698 empno, 'BLAKE'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-05-01','yyyy-mm-dd') hiredate, 2850 sal, NULL comm, 30 deptno FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-06-09','yyyy-mm-dd') hiredate, 2450 sal, NULL comm, 10 deptno FROM dual UNION ALL
    SELECT 7566 empno, 'JONES'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-04-02','yyyy-mm-dd') hiredate, 2975 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7788 empno, 'SCOTT'  ename, 'ANALYST'   job, 7566 mgr, TO_DATE('1987-04-19','yyyy-mm-dd') hiredate, 3000 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'   ename, 'ANALYST'   job, 7566 mgr, TO_DATE('1981-12-03','yyyy-mm-dd') hiredate, 3000 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7369 empno, 'SMITH'  ename, 'CLERK'     job, 7902 mgr, TO_DATE('1980-12-17','yyyy-mm-dd') hiredate, 800  sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7499 empno, 'ALLEN'  ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-02-20','yyyy-mm-dd') hiredate, 1600 sal, 300  comm, 30 deptno FROM dual UNION ALL
    SELECT 7521 empno, 'WARD'   ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-02-22','yyyy-mm-dd') hiredate, 1250 sal, 500  comm, 30 deptno FROM dual UNION ALL
    SELECT 7654 empno, 'MARTIN' ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-09-28','yyyy-mm-dd') hiredate, 1250 sal, 1400 comm, 30 deptno FROM dual UNION ALL
    SELECT 7844 empno, 'TURNER' ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-09-08','yyyy-mm-dd') hiredate, 1500 sal, 0    comm, 30 deptno FROM dual UNION ALL
    SELECT 7876 empno, 'ADAMS'  ename, 'CLERK'     job, 7788 mgr, TO_DATE('1987-05-23','yyyy-mm-dd') hiredate, 1100 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7900 empno, 'JAMES'  ename, 'CLERK'     job, 7698 mgr, TO_DATE('1981-12-03','yyyy-mm-dd') hiredate, 950  sal, NULL comm, 30 deptno FROM dual UNION ALL
    SELECT 7934 empno, 'MILLER' ename, 'CLERK'     job, 7782 mgr, TO_DATE('1982-01-23','yyyy-mm-dd') hiredate, 1300 sal, NULL comm, 10 deptno FROM dual
), dept AS (
    SELECT 10 deptno, 'ACCOUNTING' dname, 1000 locno FROM Dual UNION ALL
    SELECT 20 deptno, 'RESEARCH'   dname, 1100 locno FROM Dual UNION ALL
    SELECT 30 deptno, 'SALES'      dname, 1200 locno FROM Dual UNION ALL
    SELECT 40 deptno, 'OPERATIONS' dname, 1300 locno FROM Dual
)

SELECT *
  FROM (SELECT job
             , deptno
             , sal
          FROM emp
       )
PIVOT ( SUM(sal)
         FOR deptno IN ('10', '20', '30', '40')
       )
ORDER BY job
;

-------------------------------------
-- 예제 2
-------------------------------------
WITH emp AS (
    SELECT 7839 empno, 'KING'   ename, 'PRESIDENT' job, NULL mgr, TO_DATE('1981-11-17','yyyy-mm-dd') hiredate, 5000 sal, NULL comm, 10 deptno FROM dual UNION ALL
    SELECT 7698 empno, 'BLAKE'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-05-01','yyyy-mm-dd') hiredate, 2850 sal, NULL comm, 30 deptno FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-06-09','yyyy-mm-dd') hiredate, 2450 sal, NULL comm, 10 deptno FROM dual UNION ALL
    SELECT 7566 empno, 'JONES'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-04-02','yyyy-mm-dd') hiredate, 2975 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7788 empno, 'SCOTT'  ename, 'ANALYST'   job, 7566 mgr, TO_DATE('1987-04-19','yyyy-mm-dd') hiredate, 3000 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'   ename, 'ANALYST'   job, 7566 mgr, TO_DATE('1981-12-03','yyyy-mm-dd') hiredate, 3000 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7369 empno, 'SMITH'  ename, 'CLERK'     job, 7902 mgr, TO_DATE('1980-12-17','yyyy-mm-dd') hiredate, 800  sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7499 empno, 'ALLEN'  ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-02-20','yyyy-mm-dd') hiredate, 1600 sal, 300  comm, 30 deptno FROM dual UNION ALL
    SELECT 7521 empno, 'WARD'   ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-02-22','yyyy-mm-dd') hiredate, 1250 sal, 500  comm, 30 deptno FROM dual UNION ALL
    SELECT 7654 empno, 'MARTIN' ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-09-28','yyyy-mm-dd') hiredate, 1250 sal, 1400 comm, 30 deptno FROM dual UNION ALL
    SELECT 7844 empno, 'TURNER' ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-09-08','yyyy-mm-dd') hiredate, 1500 sal, 0    comm, 30 deptno FROM dual UNION ALL
    SELECT 7876 empno, 'ADAMS'  ename, 'CLERK'     job, 7788 mgr, TO_DATE('1987-05-23','yyyy-mm-dd') hiredate, 1100 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7900 empno, 'JAMES'  ename, 'CLERK'     job, 7698 mgr, TO_DATE('1981-12-03','yyyy-mm-dd') hiredate, 950  sal, NULL comm, 30 deptno FROM dual UNION ALL
    SELECT 7934 empno, 'MILLER' ename, 'CLERK'     job, 7782 mgr, TO_DATE('1982-01-23','yyyy-mm-dd') hiredate, 1300 sal, NULL comm, 10 deptno FROM dual
), dept AS (
    SELECT 10 deptno, 'ACCOUNTING' dname, 1000 locno FROM Dual UNION ALL
    SELECT 20 deptno, 'RESEARCH'   dname, 1100 locno FROM Dual UNION ALL
    SELECT 30 deptno, 'SALES'      dname, 1200 locno FROM Dual UNION ALL
    SELECT 40 deptno, 'OPERATIONS' dname, 1300 locno FROM Dual
)

SELECT *
  FROM (SELECT job
             , deptno
             , sal
          FROM emp
       )
PIVOT XML ( SUM(sal)
             FOR deptno IN (SELECT deptno FROM dept)
           )
ORDER BY job
;

-------------------------------------
-- 예제 3
-------------------------------------
WITH emp AS (
    SELECT 7839 empno, 'KING'   ename, 'PRESIDENT' job, NULL mgr, TO_DATE('1981-11-17','yyyy-mm-dd') hiredate, 5000 sal, NULL comm, 10 deptno FROM dual UNION ALL
    SELECT 7698 empno, 'BLAKE'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-05-01','yyyy-mm-dd') hiredate, 2850 sal, NULL comm, 30 deptno FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-06-09','yyyy-mm-dd') hiredate, 2450 sal, NULL comm, 10 deptno FROM dual UNION ALL
    SELECT 7566 empno, 'JONES'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-04-02','yyyy-mm-dd') hiredate, 2975 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7788 empno, 'SCOTT'  ename, 'ANALYST'   job, 7566 mgr, TO_DATE('1987-04-19','yyyy-mm-dd') hiredate, 3000 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'   ename, 'ANALYST'   job, 7566 mgr, TO_DATE('1981-12-03','yyyy-mm-dd') hiredate, 3000 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7369 empno, 'SMITH'  ename, 'CLERK'     job, 7902 mgr, TO_DATE('1980-12-17','yyyy-mm-dd') hiredate, 800  sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7499 empno, 'ALLEN'  ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-02-20','yyyy-mm-dd') hiredate, 1600 sal, 300  comm, 30 deptno FROM dual UNION ALL
    SELECT 7521 empno, 'WARD'   ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-02-22','yyyy-mm-dd') hiredate, 1250 sal, 500  comm, 30 deptno FROM dual UNION ALL
    SELECT 7654 empno, 'MARTIN' ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-09-28','yyyy-mm-dd') hiredate, 1250 sal, 1400 comm, 30 deptno FROM dual UNION ALL
    SELECT 7844 empno, 'TURNER' ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-09-08','yyyy-mm-dd') hiredate, 1500 sal, 0    comm, 30 deptno FROM dual UNION ALL
    SELECT 7876 empno, 'ADAMS'  ename, 'CLERK'     job, 7788 mgr, TO_DATE('1987-05-23','yyyy-mm-dd') hiredate, 1100 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7900 empno, 'JAMES'  ename, 'CLERK'     job, 7698 mgr, TO_DATE('1981-12-03','yyyy-mm-dd') hiredate, 950  sal, NULL comm, 30 deptno FROM dual UNION ALL
    SELECT 7934 empno, 'MILLER' ename, 'CLERK'     job, 7782 mgr, TO_DATE('1982-01-23','yyyy-mm-dd') hiredate, 1300 sal, NULL comm, 10 deptno FROM dual
), dept AS (
    SELECT 10 deptno, 'ACCOUNTING' dname, 1000 locno FROM Dual UNION ALL
    SELECT 20 deptno, 'RESEARCH'   dname, 1100 locno FROM Dual UNION ALL
    SELECT 30 deptno, 'SALES'      dname, 1200 locno FROM Dual UNION ALL
    SELECT 40 deptno, 'OPERATIONS' dname, 1300 locno FROM Dual
)

SELECT job
     , EXTRACTVALUE(deptno_xml, '/PivotSet/item[1]/column[2]') dept1
     , EXTRACTVALUE(deptno_xml, '/PivotSet/item[2]/column[2]') dept2
     , EXTRACTVALUE(deptno_xml, '/PivotSet/item[3]/column[2]') dept3
  FROM (SELECT job
             , deptno
             , sal
          FROM emp
       )
PIVOT XML ( SUM(sal)
             FOR deptno IN (SELECT deptno FROM dept)
           )
ORDER BY job
;

-------------------------------------
-- 예제 4
-------------------------------------
WITH emp AS (
    SELECT 7839 empno, 'KING'   ename, 'PRESIDENT' job, NULL mgr, TO_DATE('1981-11-17','yyyy-mm-dd') hiredate, 5000 sal, NULL comm, 10 deptno FROM dual UNION ALL
    SELECT 7698 empno, 'BLAKE'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-05-01','yyyy-mm-dd') hiredate, 2850 sal, NULL comm, 30 deptno FROM dual UNION ALL
    SELECT 7782 empno, 'CLARK'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-06-09','yyyy-mm-dd') hiredate, 2450 sal, NULL comm, 10 deptno FROM dual UNION ALL
    SELECT 7566 empno, 'JONES'  ename, 'MANAGER'   job, 7839 mgr, TO_DATE('1981-04-02','yyyy-mm-dd') hiredate, 2975 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7788 empno, 'SCOTT'  ename, 'ANALYST'   job, 7566 mgr, TO_DATE('1987-04-19','yyyy-mm-dd') hiredate, 3000 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7902 empno, 'FORD'   ename, 'ANALYST'   job, 7566 mgr, TO_DATE('1981-12-03','yyyy-mm-dd') hiredate, 3000 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7369 empno, 'SMITH'  ename, 'CLERK'     job, 7902 mgr, TO_DATE('1980-12-17','yyyy-mm-dd') hiredate, 800  sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7499 empno, 'ALLEN'  ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-02-20','yyyy-mm-dd') hiredate, 1600 sal, 300  comm, 30 deptno FROM dual UNION ALL
    SELECT 7521 empno, 'WARD'   ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-02-22','yyyy-mm-dd') hiredate, 1250 sal, 500  comm, 30 deptno FROM dual UNION ALL
    SELECT 7654 empno, 'MARTIN' ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-09-28','yyyy-mm-dd') hiredate, 1250 sal, 1400 comm, 30 deptno FROM dual UNION ALL
    SELECT 7844 empno, 'TURNER' ename, 'SALESMAN'  job, 7698 mgr, TO_DATE('1981-09-08','yyyy-mm-dd') hiredate, 1500 sal, 0    comm, 30 deptno FROM dual UNION ALL
    SELECT 7876 empno, 'ADAMS'  ename, 'CLERK'     job, 7788 mgr, TO_DATE('1987-05-23','yyyy-mm-dd') hiredate, 1100 sal, NULL comm, 20 deptno FROM dual UNION ALL
    SELECT 7900 empno, 'JAMES'  ename, 'CLERK'     job, 7698 mgr, TO_DATE('1981-12-03','yyyy-mm-dd') hiredate, 950  sal, NULL comm, 30 deptno FROM dual UNION ALL
    SELECT 7934 empno, 'MILLER' ename, 'CLERK'     job, 7782 mgr, TO_DATE('1982-01-23','yyyy-mm-dd') hiredate, 1300 sal, NULL comm, 10 deptno FROM dual
), dept AS (
    SELECT 10 deptno, 'ACCOUNTING' dname, 1000 locno FROM Dual UNION ALL
    SELECT 20 deptno, 'RESEARCH'   dname, 1100 locno FROM Dual UNION ALL
    SELECT 30 deptno, 'SALES'      dname, 1200 locno FROM Dual UNION ALL
    SELECT 40 deptno, 'OPERATIONS' dname, 1300 locno FROM Dual
)

SELECT *
  FROM (SELECT job
             , deptno
             , sal
          FROM emp
       )
PIVOT ( SUM(sal)
         FOR deptno IN ('10' AS d10, '20' AS d20, '30' AS d30, '40' AS d40)
       )
ORDER BY job
;

-------------------------------------
-- 예제 5
-------------------------------------
CREATE OR REPLACE PROCEDURE pc_dynamic_pivot(p_cursor in out sys_refcursor)
AS
    sql_param VARCHAR2(1000);
    sql_query VARCHAR2(4000);
BEGIN
    FOR x IN (SELECT deptno FROM dept ORDER BY 1)
    LOOP
       sql_param := sql_param || '''' || x.deptno || '''' || ', ';
    END LOOP;

    sql_param := SUBSTR(sql_param, 1, LENGTH(sql_param)-2);

    sql_query := sql_query || ' SELECT *                                    ';
    sql_query := sql_query || '   FROM (SELECT job                          ';
    sql_query := sql_query || '              , deptno                       ';
    sql_query := sql_query || '              , sal                          ';
    sql_query := sql_query || '           FROM emp                          ';
    sql_query := sql_query || '        )                                    ';
    sql_query := sql_query || '  PIVOT ( SUM(sal)                           ';
    sql_query := sql_query || '          FOR deptno IN (' || sql_param || ')';
    sql_query := sql_query || '        )                                    ';
    sql_query := sql_query || 'ORDER BY job                                 ';

    open p_cursor for sql_query;
END;

프로시저 호출
var rc refcursor
exec pc_dynamic_pivot(:rc)
print rc

728x90

저작자표시 비영리 동일조건

'데이터베이스 > 오라클' 카테고리의 다른 글

ROW를 한줄로 (0)	2021.09.09
SQL기초_컬럼삭제,수정,추가Comment추가 수정 삭제 등등 (0)	2021.09.09
오라클 환경설정 파일 spfile, pfile (0)	2021.09.09
리눅스 오라클 sqlplus history 기능 사용하기 (0)	2021.09.09
윈도우 서버용 오라클 SID 수정 방법 (0)	2021.09.09

C.B.W

오라클 PIVOT 동정 컬럼 사용방법(가변IN절)

일반적인 PIVOT 쿼리

PIVOT XML을 사용하는 방법

Dynamic SQL을 사용하는 방법

사용 예제

'데이터베이스 > 오라클' 카테고리의 다른 글

댓글

티스토리툴바

오라클 PIVOT 동정 컬럼 사용방법(가변IN절)

일반적인 PIVOT 쿼리

PIVOT XML을 사용하는 방법

Dynamic SQL을 사용하는 방법

사용 예제

'데이터베이스 > 오라클' 카테고리의 다른 글

관련글

댓글

티스토리툴바