[Oracle] Oracle Pivot사용법

oracle pivot을 사용할 때 마다 헷갈리고 계속 찾아보게 되어서 한번 재대로 정리해서 완벽하게 이해하고 가고싶어 정리해본다.

1. pivot이란.

oracle에서 행(row)으로 출력되는 데이터를 열(column)형식으로 출력하는 방법을 말한다.

2. 예제 (pivot)

pivot을 설명해주는 다른 블로그에서도 대부분 scott의 emp테이블을 예시로 설명하곤 한다. 나도 똑같이 scott의 emp테이블을 통해 여러가지 예제를 만들어
이해하기 쉽게 설명을 도와주겠다.

우선 pivot을 사용하려면 기준되는 컬럼을 2가지 설정해야한다. 

ex) dept(부서)별 month(월)별 [기준컬럼] -> 입사 건수
ex) dept(부서)별 job(직책)별 [기준컬럼] -> 급여 합계
ex) dept(부서)별 job(직책)별 [기준컬럼] -> 급여 평균

이런식으로 기준이 되는 컬럼을 2가지 설정하고, 그 중 어떤 항목이 가로로 이동할 지 결정한다.

나는 여기서 'dept(부서)별 job(직책)별 급여 합계'를 조회할 것이다. 

SELECT deptno AS "부서별", job AS "직책별", sal
FROM scott.emp;

 

 

여기서 dept(부서)는 [세로]로 기준을 잡을 것이고, job(직책)은 [가로]로 기준을 잡을 것이다.


pivot형식은 아래와 같다.

select *
from ( <pivot대상 테이블> )
pivot(
	<집계함수> for <가로로 변경할 column> in ([가로로 출력되는 column의 값 list] AS alias, ...)
)

위 포멧에서 pivot대상 테이블은 아래 쿼리와 같다. 즉 '조회 뷰'라고 생각하면 된다.

SELECT deptno AS "부서별", job AS "직책별", sal
FROM scott.emp;

위 포멧에 맞춰 sql문을 작성하자면

SELECT *
FROM (
	SELECT 
		deptno, 
		job,
		sal
	FROM scott.emp
	)
pivot (
	avg(sal) FOR job IN ('CLERK', 'SALESMAN', 'ANALYST', 'MANAGER', 'PRESIDENT')
	)

위와 같이 결과를 얻을 수 있다.

3. 예시 (기준이 되는 컬럼이 3가지 일때)

ex) [테이블별 privilege별][세로] [user별][가로] ->  권한여부

==> 테이블별[세로]  user별[가로] -> privilege

위와 같이 기준컬럼이 3가지 일 경우 방법이 있다.

3-1. 기준컬럼을 2개로 줄이기

테이블별(row) user별(column) privilege로 정의할 수 있다.

이 기준에 따라 sql문을 작성하자면

SELECT *
FROM (
	SELECT 
		owner || '.' || table_name AS "테이블별",
		grantee ,
		privilege
	FROM dba_tab_privs	
	)
pivot (
	listagg(privilege, ', ') FOR grantee IN ('SYS', 'SYSTEM')
)	;

 

결과는 위와 같다.

3-2. 기준컬럼 3개로 만들기

테이블별(row) priviliege별(row) user(column) 으로 정의할 수 있다.

이 기준에 따라 sql문을 작성하면

SELECT *
FROM (
	SELECT 
		owner || '.' || table_name AS "테이블별",
		privilege AS "권한별",
		grantee,
		'true' AS is_true
	FROM dba_tab_privs	
	)
pivot (
	max(is_true) FOR grantee IN ('SYS', 'SYSTEM')
);

위와 같이 결과물을 얻을 수 있다.

pivot에 꼭 정답이 있는 것은 아니다. 본인이 어떻게 표현하고자 하는지 입맛에 따라 여러 예시를 통해 숙달하여 작성해보도록 하자.