Oracle行转列语法总结

文章目录

一.decode语法
二.CASE语法
三.PIVOT语法
**小结:**
四.wm_contact语法
五.listagg语法
六.xmlagg语法
**小结:**

备注:测试以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_SALESMANFROM 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_SALESMANFROM empGROUP 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_SALESMANFROM tmp

小结:

decode 语法简单，Oracle独有
case sql标准语法
pivot 语法最为简单，Oracle、sqlserver、postgresql均可以使用

下面再来讲讲wm_concat、listagg、xmlagg
需求:部门编号为20的所有的员工信息，以行的形式显示

四.wm_contact语法

SELECT T.DEPTNO, wm_concat(t.ename) namesFROM EMP TWHERE T.DEPTNO = \'20\'GROUP BY T.DEPTNO;

五.listagg语法

SELECT T.DEPTNO,listagg(T.ENAME, \',\') WITHIN GROUP(ORDER BY T .ENAME) namesFROM EMP TWHERE T.DEPTNO = \'20\'GROUP BY T.DEPTNO;

六.xmlagg语法

SELECT T.DEPTNO,xmlagg(XMLELEMENT(T, \',\',T.ENAME) ORDER BY T .ENAME).EXTRACT (\'//text()\') namesFROM EMP TWHERE T.DEPTNO = \'20\'GROUP BY T.DEPTNO;