CASE 표현과 Oracle의 DECODE 함수의 차이점 비교 - SQL전문가 가이드 p.264

p. 264 – CASE 표현과 Oracle의 DECODE 함수의 차이점 비교

SELECT DEPTNO,

         AVG(CASE MONTH WHEN 1 THEN SAL END) M01,

         AVG(CASE MONTH WHEN 2 THEN SAL END) M02,

AVG(CASE MONTH WHEN 3 THEN SAL END) M03,

AVG(CASE MONTH WHEN 4 THEN SAL END) M04,

AVG(CASE MONTH WHEN 5 THEN SAL END) M05,

AVG(CASE MONTH WHEN 6 THEN SAL END) M06,

AVG(CASE MONTH WHEN 7 THEN SAL END) M07,

AVG(CASE MONTH WHEN 8 THEN SAL END) M08,

AVG(CASE MONTH WHEN 9 THEN SAL END) M09,

AVG(CASE MONTH WHEN 10 THEN SAL END) M10,

AVG(CASE MONTH WHEN 11 THEN SAL END) M11,

AVG(CASE MONTH WHEN 12 THEN SAL END) M12

FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL

       FROM EMP)

GROUP BY DEPTNO;

 

SELECT DEPTNO,

         AVG(DECODE(MONTH, 1, SAL)) M01, AVG(DECODE(MONTH, 2, SAL)) M02,

AVG(DECODE(MONTH, 3, SAL)) M03, AVG(DECODE(MONTH, 4, SAL)) M04,

AVG(DECODE(MONTH, 5, SAL)) M05, AVG(DECODE(MONTH, 6, SAL)) M06,

AVG(DECODE(MONTH, 7, SAL)) M07, AVG(DECODE(MONTH, 8, SAL)) M08,

AVG(DECODE(MONTH, 9, SAL)) M09, AVG(DECODE(MONTH, 10, SAL)) M10,

AVG(DECODE(MONTH, 11, SAL)) M11, AVG(DECODE(MONTH, 12, SAL)) M12

FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL

       FROM EMP)

GROUP BY DEPTNO;