SELECT deptno, nvl(sum(case when job = 'MANAGER' then sal else 0 end),0) s_MANAGER, nvl(sum(case when job = 'ANALYST' then sal else 0 end),0) s_ANALYST, nvl(sum(case when job = 'CLERK' then sal else 0 end),0) s_CLERK, nvl(sum(case when job = 'PRESIDENT' then sal else 0 end),0) s_PRESIDENT, nvl(sum(case when job = 'SALESMAN' then sal else 0 end),0) s_SALESMAN FROM emp?GROUP?BY?deptno;
?
三、PIVOT語法
?
?
?
?
?
?
?
?
WITH p AS (SELECT deptno, job, sal FROM emp)SELECT * FROM p pivot(SUM(sal) FOR job IN('MANAGER' AS s_MANAGER, 'ANALYST' AS s_ANALYST, 'CLERK' AS s_CLERK, 'PRESIDENT' AS s_PRESIDENT, 'SALESMAN' AS s_SALESMAN));
?
不過這個地方null值沒有替換成0，要通過nvl再轉換一下。
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
WITH p AS (SELECT deptno, job, sal FROM emp),tmp AS (SELECT * FROM p pivot(SUM(sal) FOR job IN('MANAGER' AS s_MANAGER, 'ANALYST' AS s_ANALYST, 'CLERK' AS s_CLERK, 'PRESIDENT' AS s_PRESIDENT, 'SALESMAN' AS s_SALESMAN)))SELECT deptno, nvl(s_MANAGER, 0) s_MANAGER, nvl(s_ANALYST, 0) s_ANALYST, nvl(s_CLERK, 0) s_CLERK, nvl(s_PRESIDENT, 0) s_PRESIDENT, nvl(s_SALESMAN, 0) s_SALESMAN FROM tmp
?
小結：
decode 語法簡單，Oracle獨有。 case sql標準語法。 pivot 語法最為簡單，Oracle、sqlserver、postgresql均可以使用。
?
下面再來講講wm_concat、listagg、xmlagg。
?
需求：部門編號為20的所有的員工信息，以行的形式顯示。
?
四、wm_contact語法
?
?
?
?
SELECT T.DEPTNO, wm_concat(t.ename) names FROM EMP T WHERE T.DEPTNO = '20' GROUP BY T.DEPTNO;
?
?
五、listagg語法
?
?
?
?
?
SELECT T.DEPTNO, listagg(T.ENAME, ',') WITHIN GROUP(ORDER BY T .ENAME) names FROM EMP T WHERE T.DEPTNO = '20' GROUP BY T.DEPTNO;
六、xmlagg語法
?
?
?
?
?
SELECT T.DEPTNO, xmlagg(XMLELEMENT(T, ',',T.ENAME) ORDER BY T .ENAME).EXTRACT ('//text()') names FROM EMP T WHERE T.DEPTNO = '20' GROUP BY T.DEPTNO;
