데이터베이스 피벗 사용해보기

피벗 PIVOT 란?

피벗은 행을 열로 변환할 때 사용하는 기법을 말한다. (테이블의 회전)

 

 

 

select deptno,job, sal from emp
where deptno = 30
order by deptno;

출력하고싶은 데이터

deptno CLERK SALESMAN MANAGER

  30       950      4600        2850

 

 

 

---

 

부서별, 직업별 월급의 합을 출력하고 싶을 때

 

select deptno, job, sum(sal)
from emp
group by deptno, job
order by deptno, job;

 

 

피벗해서 데이터를 달라고 한다면?

select deptno,
sum(decode(job, 'CLERK', sal,0)) CLERK,
sum(decode(job, 'MANAGER', sal,0)) MANAGER,
sum(decode(job, 'PRESIDENT', sal,0)) PRESIDENT,
sum(decode(job, 'ANALYST', sal,0)) ANALYST,
sum(decode(job, 'SALESMAN', sal,0)) SALESMAN,
sum(sal) total
from emp
group by deptno
order by deptno;

---

pivot 함수 사용

 

1. 피벗 함수를 사용할 때에는 가져올 데이터를 정확하게 입력해주어야 한다.

SELECT * 
FROM (SELECT DEPTNO, JOB, SAL from emp);

 

2. 무엇으로 그룹핑을 할지 정한다.

SELECT * 
FROM (SELECT DEPTNO, JOB, SAL from emp)
PIVOT
(
    SUM(SAL)
)
ORDER BY deptno;

 

3. 가로로 출력하고 싶은 데이터를 정한다. ( FOR JOB in ('A','B','C'))

SELECT * 
FROM (SELECT DEPTNO, JOB, SAL from emp)
PIVOT
(
    SUM(SAL) FOR JOB in ('CLERK','MANAGER','PRESIDENT','ANALYST','SALESMAN')
)
ORDER BY deptno;

 

 

---

포지션별로 교수의 수가 몇명인지 확인하기

 

SELECT *
FROM professor;

SELECT position, COUNT(position)
FROM professor
GROUP BY position
ORDER BY position;

 

DECODE

SELECT sum(decode(position,'전임강사',count(position),0)) 전임강사
,sum(decode(position,'정교수',count(position),0)) 정교수
,sum(decode(position,'조교수',count(position),0)) 조교수
from professor
group by position;

 

PIVOT

SELECT *
FROM (SELECT position FROM professor)
PIVOT
(
count(position) for position IN('전임강사','정교수','조교수')
);

---

달력 출력하기 

SELECT *
FROM CAL;

 

DECODE 

SELECT WEEK, 
sum(decode(day,'일',num_day,0)) 일,
sum(decode(day,'월',num_day,0)) 월,
sum(decode(day,'화',num_day,0)) 화,
sum(decode(day,'수',num_day,0)) 수,
sum(decode(day,'목',num_day,0)) 목,
sum(decode(day,'금',num_day,0)) 금,
sum(decode(day,'토',num_day,0)) 토
from cal
group by week
order by week;

PIVOT

select *
from (select week,day,num_day from cal)
pivot(
sum(num_day) for day in('일','월','화','수','목','금','토')
)
order by week;