ORACLE 11g PIVOT
from | 오라클과 함께 | http://blog.naver.com/neptuneosy.do
1. PIVOT
11g NEW FEATURES 중의 하나이다. 종(세로) 의 결과를 횡(가로)로 표현하게 해준다.
기존에 이런 결과를 구현하기 위해선 DECODE 를 사용해 구현을 했다.
2. 예제
샘플 스키마 SCOTT 을 사용하였다. emp 와 dept 를 사용하였고 목적은 JOB 별로 각
부서에 몇 명씩 소속되어있는지를 구하는 쿼리이다.
이를테면 MANAGER 의 경우 ACCOUNTING, SALES, RESEACH 에 각각 몇 명 씩 있는지
그 결과를 구한다.
A. 기본쿼리
SQL> get step1
1 select d.dname, e.job,count(e.empno) member
2 from emp e, dept d
3 where e.deptno = d.deptno
4 group by d.dname,e.job
5* order by d.dname
SQL> @STEP1
DNAME JOB MEMBER
-------------- --------- ----------
ACCOUNTING CLERK 1
ACCOUNTING MANAGER 1
ACCOUNTING PRESIDENT 1
RESEARCH ANALYST 2
RESEARCH CLERK 2
RESEARCH MANAGER 1
SALES CLERK 1
SALES MANAGER 1
SALES SALESMAN 4
9 개의 행이 선택되었습니다.
위 쿼리를 보면 JOB – DNAME 별로 각기 몇 명의 직원이 있는지 확인할 수 있다.
B. DECODE 를 사용한 예제
SQL> get step2
1 select dname,
2 decode(job,'CLERK',member,0) clerk,
3 decode(job,'MANAGER',member,0) manager,
4 decode(job,'PRESIDENT',member,0) president,
5 decode(job,'ANALYST',member,0) analyst,
6 decode(job,'SALESMAN',member,0) salesman
7 from (
8 select e.job, d.dname,count(e.empno) member
9 from emp e, dept d
10 where e.deptno = d.deptno
11 group by d.dname,e.job
12* order by d.dname)
SQL> /
DNAME CLERK MANAGER PRESIDENT ANALYST SALESMAN
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING 1 0 0 0 0
ACCOUNTING 0 1 0 0 0
ACCOUNTING 0 0 1 0 0
RESEARCH 0 0 0 2 0
RESEARCH 2 0 0 0 0
RESEARCH 0 1 0 0 0
SALES 1 0 0 0 0
SALES 0 1 0 0 0
SALES 0 0 0 0 4
9 개의 행이 선택되었습니다.
decode 를 사용한 예이다. A 에서 초기 결과를 뽑아낸 쿼리와 인라인뷰의 쿼리는 동일하다. 인라인뷰의 DNAME 을 제외한 JOB과 MEMBER 값은 DECODE 에서 가공된다.
6 decode(job,'SALESMAN',member,0) salesman
JOB 에 해당하는 값을 매칭시켜 해당하는 값에 MEMBER (COUNT 한 결과) 값을 출력한다.
A 와 B 의 결과 ROWS 수는 동일하다. DECODE 를 통해 각 ROW 에 대해 JOB 이름을 확인하고 펼쳐놓은 것에 불과하다. 인라인뷰의 결과값을 다시 사용하기 때문에 각 DEPARTMENTS 의 각각의 JOB 별로 값을 하나씩 가지고 있다. DEPART-JOB 별로 값을 하나씩 가지고 있기 때문에 위의 예제는 아래와 같이 개선될 수 있다.
SQL> get step3
1 select dname,
2 max(decode(job,'CLERK',member,0)) clerk,
3 max(decode(job,'MANAGER',member,0)) manager,
4 max(decode(job,'PRESIDENT',member,0)) president,
5 max(decode(job,'ANALYST',member,0)) analyst,
6 max(decode(job,'SALESMAN',member,0)) salesman
7 from (
8 select e.job, d.dname,count(e.empno) member
9 from emp e, dept d
10 where e.deptno = d.deptno
11 group by d.dname,e.job
12 order by d.dname)
13* group by dname
SQL> /
DNAME CLERK MANAGER PRESIDENT ANALYST SALESMAN
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING 1 1 1 0 0
RESEARCH 2 1 0 2 0
SALES 1 1 0 0 4
C. PIVOT 을 사용한 예제
SQL> get step4
1 select * from (
2 select e.job, d.dname,e.empno
3 from emp e, dept d
4 where e.deptno = d.deptno)
5 pivot (
6 count(empno)
7 for job in ('CLERK','MANAGER','PRESIDENT','ANALYST','SALESMAN')
8* )
SQL> /
DNAME 'CLERK' 'MANAGER' 'PRESIDENT' 'ANALYST' 'SALESMAN'
-------------- ---------- ---------- ----------- ---------- ----------
ACCOUNTING 1 1 1 0 0
RESEARCH 2 1 0 2 0
SALES 1 1 0 0 4
2번째 줄을 보면 select-list 에 필요한 컬럼을 다 지정한 것을 확인할 수 있다.
그 후 5~7 이 실질적으로 pivot을 사용한 부분으로 count(empno) 을 job 이 어떠한
상태일 때 각기 표시할 것인지(7번째 줄) 정의되어 있다.
*자료를 제공해 준 Neptuneosy 님께 감사~ 이번 예제 진행하면서 decode 의 또다른 활용에 대해 알게 된 좋은 경험이었다.*