一、decode語法

• ?
• ?
• ?
• ?
• ?
• ?
• ?
• ?

SELECT deptno,       nvl(SUM(decode(job, 'MANAGER', sal)), 0) s_MANAGER,       nvl(SUM(decode(job, 'ANALYST', sal)), 0) s_ANALYST,       nvl(SUM(decode(job, 'CLERK', sal)), 0) s_CLERK,       nvl(SUM(decode(job, 'PRESIDENT', sal)), 0) s_PRESIDENT,       nvl(SUM(decode(job, 'SALESMAN', sal)), 0) s_SALESMAN  FROM empGROUP?BY?deptno;

二、CASE語法

• ?
• ?
• ?
• ?
• ?
• ?
• ?
• ?

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;

?

小結：

wm_concat 語法最簡單，但是默認是 clob列，plsql不容易導出。
listtagg 語法稍微復雜，但是默認是字符串，性能會比wm_concat 好，但是超過4000個字符，受限制。
xmlagg 字符串超過4000字符,就需要使用xmlagg