oracle+xmlagg+行转列,Oracle行转列小结

备注:测试以Oracle 11g下的scoot schema为例

需求:求emp表各个岗位的工资之和，如无，用0代替

如需要scott用户下建表及录入数据语句，可参考:

scott建表及录入数据sql脚本

一.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 emp

GROUP BY deptno;

image.png

二.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;

image.png

三.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));

image.png

不过这个地方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

image.png

小结:

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;

image.png

五.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;

image.png

六.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;

image.png

小结:

wm_concat 语法最简单，但是默认是 clob列，plsql不容易导出

listtagg 语法稍微复杂，但是默认是字符串，性能会比wm_concat 好，但是超过4000个字符，受限制。

xmlagg 字符串超过4000字符,就需要使用xmlagg