墨墨导读：本文来自墨天轮用户“只是甲”的投稿，总结所有Oracle行转列的语法，供大家参考学习。
墨天轮主页：https://www.modb.pro/u/372619
注：本文测试以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;
小结：
wm_concat 语法最简单，但是默认是 clob列，plsql不容易导出。
listtagg 语法稍微复杂，但是默认是字符串，性能会比wm_concat 好，但是超过4000个字符，受限制。
xmlagg 字符串超过4000字符,就需要使用xmlagg。
作者
曾庆顺，10年数据库运维、数据仓库及大数据经验，擅长Oracle、MySQL、Hive，具有Oracle 10g OCP，Linux RHCE，长期服务于通信、金融信贷行业。
墨天轮原文链接：https://www.modb.pro/db/26033（复制到浏览器打开或者点击“阅读原文”立即查看）
推荐阅读：267页！2020年度数据库技术年刊
推荐下载：2020数据技术嘉年华PPT下载
2020数据技术嘉年华近50个PPT下载、视频回放已上传墨天轮平台，可在“数据和云”公众号回复关键词“2020DTC”获得！
视频号，新的分享时代，关注我们，看看有什么新发现？
数据和云
ID：OraNews
如有收获，请划至底部，点击“在看”，谢谢！
点击下图查看更多 ↓
云和恩墨大讲堂 | 一个分享交流的地方
长按，识别二维码，加入万人交流社群
请备注：云和恩墨大讲堂
点个“在看”
你的喜欢会被看到❤
转载：https://blog.csdn.net/enmotech/article/details/114695801