오라클 피벗 PIVOT 대신 직접짜기

원본테이블

SELECT *
FROM CAL;

 

 

요일을 열이름으로 하여 달력만들기

SELECT
MAX(DECODE(DAY, '일', NUM_DAY)) "일", 
MAX(DECODE(DAY, '월', NUM_DAY)) "월",
MAX(DECODE(DAY, '화', NUM_DAY)) "화", 
MAX(DECODE(DAY, '수', NUM_DAY)) "수",
MAX(DECODE(DAY, '목', NUM_DAY)) "목", 
MAX(DECODE(DAY, '금', NUM_DAY)) "금",
MAX(DECODE(DAY, '토', NUM_DAY)) "토"
FROM CAL
GROUP BY WEEK
ORDER BY WEEK;

 

 

EMP 원본

SELECT *
FROM EMP;

 

 

직업별 부서별 인원수

SELECT DEPTNO, 
COUNT(DECODE(JOB, 'CLERK', 0)) "CLERK",
COUNT(DECODE(JOB, 'MANAGER', 0)) "MANAGER",
COUNT(DECODE(JOB, 'PRESIDENT', 0)) "PRESIDENT",
COUNT(DECODE(JOB, 'ANALYST', 0)) "ANALYST",
COUNT(DECODE(JOB, 'SALESMAN', 0)) "SALESMAN"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

 

 

PROFESSOR 원본

SELECT *
FROM PROFESSOR;

 

 

교수직책별 평균급여

SELECT 
FLOOR(AVG(DECODE(POSITION, '정교수', PAY))) "정교수",
FLOOR(AVG(DECODE(POSITION, '조교수', PAY))) "조교수",
FLOOR(AVG(DECODE(POSITION, '전임강사', PAY))) "전임강사"
FROM PROFESSOR;