오라클에서 PIVOT을 사용하다보면 IN절에 동적으로 값을 할당하고 싶을때가 있다. 그러나 PIVOT의 IN절은 SQL의 IN절과 다르다. PIVOT의 IN절은 할당된 값의 개수와 순서를 맞춰서 결과가 출력된다. 일반적인 쿼리문의 PIVOT은 IN절에 동적으로 값을 할당 할 수 없지만, PIVOT XML 또는 Dynamic SQL을 활용하여 동적 PIVOT을 구현 할 수 있다. 일반적인 PIVOT 쿼리
PIVOT 쿼리는 IN절 ('10', '20', '30', '40')의 값과 순서에 따라서 결과가 출력된다. '40'이라는 값이 테이블에 존자하지 않아도 결과에 NULL로 출력이 된다. PIVOT XML을 사용하는 방법PIVOT XML (IN절에 서브쿼리를 사용가능)
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로 리턴됨
EXTRACTVALUE 함수를 사용하여 XML 값을 순서대로 값을 추출
PIVOT은 IN절에 할당된 인자 값이 컬럼명으로 출력이 되지만, PIVOT XML은 쿼리문에 작성한 별칭(dept1...)이 출력된다. Dynamic SQL을 사용하는 방법Dynamic SQL을 사용하는 Procedure를 생성하여 사용하는 방법
Dynamic SQL을 사용하면 기존 PIVOT와 동일한 결과가 출력되지만 Procedure를 생성해야하는 번거로움이 있다. 사용 예제PIVOT XML을 사용하는 방법Dynamic SQL을 사용하는 방법사용_예제_쿼리.txt 0.01MB |