[Oracle] 오라클 PIVOT 동적 컬럼 사용방법 (가변 IN절)

오라클에서 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를 생성해야하는 번거로움이 있다.

 

 

사용 예제

 

PIVOT XML을 사용하는 방법

 

 

Dynamic SQL을 사용하는 방법

 

 

사용_예제_쿼리.txt

0.01MB