Oracle行转列语法总结

Table of Contents

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

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

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

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