系列文章目录
【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
文章目录
前言
本篇文章讲解的主要内容是：目前Oracle支持的行列互换有两种方式：case when、pivot\unpivot,我将通过几个案例来给大家详解如何通过这两种方式实现“行转列”，“列转行”的需求，并通过执行计划看case when、pivot\unpivot二者的底层逻辑关系以及效率上的影响。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、行转列
"行转列"的这个写法，在做报表或语句改写时要经常用到,是一个非常重要的语句。
在Oracle中,有
CASE WHEN END
和Oracle11g新增的pivot函数两种方式。其中：
- CASE WHEN END编写和维护较麻烦,但适合的场景较多。
- PIVOT编写和维护简单,但有较大限制。
下面简单介绍这两种方法。
现在我有这么一个需求：
对emp表按job分组汇总,每个部门显示为一列。首先看一下最常用的
CASE WHEN END
的方法。
根据不同的条件来取值,就可以把数据分为几列。
SQL>select a.job,2casewhen a.deptno=10then sal endas d10,3casewhen a.deptno=20then sal endas d20,4casewhen a.deptno=30then sal endas d30
5from emp a
6orderby1;
JOB D10 D20 D30
--------- ---------- ---------- ----------
ANALYST 3000
CLERK 800
CLERK 950
CLERK 1100
MANAGER 2450
MANAGER 2975
MANAGER 2850
MGR
PRESIDENT 5000
SALESMAN 1250
SALESMAN 1600
SALESMAN 1250
SALESMAN 1500
sdf 130017rows selected
只是这样的数据看上去杂乱无章,需要再按job分组汇总,所以一般"行转列"语句里都会有聚集函数,就是为了把同类数据转为一行显示。
另外,要注意最后一列,我们增加了合计工资的显示,这在后面介绍的PIVOT函数中是做不到的,PIVOT 函数只能按同一个规则分类各数据,各列之间的数据不能交叉重复。
SQL>select a.job,2sum(casewhen a.deptno=10then sal end)as d10,3sum(casewhen a.deptno=20then sal end)as d20,4sum(casewhen a.deptno=30then sal end)as d30,5sum(sal)as sm
6from emp a
7groupby job
8orderby1;
JOB D10 D20 D30 SM
--------- ---------- ---------- ---------- ----------
ANALYST 30003000
CLERK 19009502850
MANAGER 2450297528508275
MGR
PRESIDENT 50005000
SALESMAN 56005600
sdf 130013008rows selected
下面看一下Oracle11g新增的"行转列"函数PIVOT,对简单的PIVOT环境提供了简单的实现方法。
SQL>SQL>select*from(2--先查询出来要进行操作的数据3select job,sal,deptno from emp
4)pivot(5sum(sal)as sm /*这里写聚集函数以及起别名，SUM、MAX等聚集函数＋列别名,若不设置别名,则后面生成的列名字默认只使用后面in里设的别名,否则两个别名相加*/6for deptno in(710as d10,/*这里等价于前面的查询：sum(case when deptno =10 then sal end) as d10,列名称为d10_sm，这个sm是前面聚集函数的别名*/820as d20,/*这里等价于前面的查询：sum(case when deptno =20 then sal end) as d20*/930as d30/*这里等价于前面的查询：sum(case when deptno =30 then sal end) as d30*/10)11)12orderby1;
JOB D10_SM D20_SM D30_SM
--------- ---------- ---------- ----------
ANALYST 3000
CLERK 1900950
MANAGER 245029752850
MGR
PRESIDENT 5000
SALESMAN 5600
sdf 13008rows selected
大家可以看一下两种方式的对比,如果还要增加提成的返回,用PIVOT则只需要增加一个设定即可。
SQL>select*from(2--先查询出来要进行操作的数据3select job,sal,comm,deptno from emp
4)pivot(5sum(sal)as sm,/*这里写聚集函数以及起别名，SUM、MAX等聚集函数＋列别名,若不设置别名,则后面生成的列名字默认只使用后面in里设的别名,否则两个别名相加*/6sum(comm)as sc /*这里写聚集函数以及起别名，SUM、MAX等聚集函数＋列别名,若不设置别名,则后面生成的列名字默认只使用后面in里设的别名,否则两个别名相加*/7for deptno in(810as d10,/*这里等价于前面的查询：sum(case when deptno =10 then sal end) as d10,sum(case when deptno =10 then comm end) as d10,列名称为d10_sc，这个sm是前面聚集函数的别名*/920as d20,/*这里等价于前面的查询：sum(case when deptno =20 then sal end) as d20*/1030as d30/*这里等价于前面的查询：sum(case when deptno =30 then sal end) as d30*/11)12)13orderby1;
JOB D10_SM D10_SC D20_SM D20_SC D30_SM D30_SC
--------- ---------- ---------- ---------- ---------- ---------- ----------
ANALYST 3000
CLERK 1900950
MANAGER 245029752850
MGR
PRESIDENT 5000
SALESMAN 56002200
sdf 13008rows selected
如果用CASE WHEN要增加三行语句。
SQL>select a.job,2sum(casewhen a.deptno=10then sal end)as d10_sm,3sum(casewhen a.deptno=10then sal end)as d10_sc,4sum(casewhen a.deptno=20then sal end)as d20_sm,5sum(casewhen a.deptno=20then sal end)as d20_sc,6sum(casewhen a.deptno=30then sal end)as d30_sm,7sum(casewhen a.deptno=30then sal end)as d30_sc
8from emp a
9groupby job
10orderby1;
JOB D10_SM D10_SC D20_SM D20_SC D30_SM D30_SC
--------- ---------- ---------- ---------- ---------- ---------- ----------
ANALYST 30003000
CLERK 19001900950950
MANAGER 245024502975297528502850
MGR
PRESIDENT 50005000
SALESMAN 56005600
sdf 130013008rows selected
PIVOT一次只能按一个条件来完成"行转列",如果同时把工作与部门都转为列,并汇总为一行时,PIVOT就无能为力了,这时只能用CASE WHEN。
SQL>select2casewhen deptno=10then ename endas d10,3casewhen deptno=20then ename endas d20,4casewhen deptno=30then ename endas d30,5casewhen job='ANALYST'then ename endas ANALYST,6casewhen job='CLERK'then ename endas CLERK,7casewhen job='MANAGER'then ename endas MANAGER,8casewhen job='MGR'then ename endas MGR,9casewhen job='PRESIDENT'then ename endas PRESIDENT,10casewhen job='SALESMAN'then ename endas SALESMAN
11from emp;
D10 D20 D30 ANALYST CLERK MANAGER MGR PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
zhaoyd zhaoyd
er er
ALLEN ALLEN
WARD WARD
JONES JONES
MARTIN MARTIN
BLAKE BLAKE
CLARK CLARK
KING KING
TURNER TURNER
ADAMS ADAMS
JAMES JAMES
FORD FORD
sdf
17rows selected
SQL>
最后分析一下PIVOT的PLAN(用
dbms_xplan.display_cursor
看)：
select/*zydtest*/*from(--先查询出来要进行操作的数据select job,sal,comm,deptno from emp
)pivot(sum(sal)as sm /*这里写聚集函数以及起别名，SUM、MAX等聚集函数＋列别名,若不设置别名,则后面生成的列名字默认只使用后面in里设的别名,否则两个别名相加*/for deptno in(10as d10,/*这里等价于前面的查询：sum(case when deptno =10 then sal end) as d10,sum(case when deptno =10 then comm end) as d10,列名称为d10_sc，这个sm是前面聚集函数的别名*/20as d20,/*这里等价于前面的查询：sum(case when deptno =20 then sal end) as d20*/30as d30 /*这里等价于前面的查询：sum(case when deptno =30 then sal end) as d30*/))orderby1;select*from v$sql aa where aa.SQL_TEXT like'%zydtest%';SQL>select*fromtable(dbms_xplan.display_cursor('5ss3y129x7p0a',0,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5ss3y129x7p0a, child number 0-------------------------------------select/*zydtest*/*from(--�������������������������� select
job,sal,comm,deptno from emp )pivot(sum(sal)as sm
/*��������������������������SUM��MAX������������������,������������,����
����������������������������in����������,����������������*/for deptno
in(10as d10,/*����������������������sum(case when deptno =10 then
sal end) as d10,sum(case when deptno =10 then comm end) as
d10,��������d10_sc������sm��������������������*/20as
d20,/*����������������������sum(case when deptno =20 then sal end) as
d20*/30as d30 /*��������
Plan hash value: 1018027214
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT GROUP BY PIVOT| | 19 | 285 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 19 | 285 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$117FC0EF
2 - SEL$117FC0EF / EMP@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$117FC0EF")
MERGE(@"SEL$F5BB74E1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$F5BB74E1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$117FC0EF" "EMP"@"SEL$2")
END_OUTLINE_DATA
*/Column Projection Information (identified by operation id):
-----------------------------------------------------------1-(#keys=2) "JOB"[VARCHAR2,9], "COMM"[NUMBER,22], SUM(CASE WHEN("DEPTNO"=10)THEN"SAL"END)[22],SUM(CASEWHEN("DEPTNO"=20)THEN"SAL"END)[22],SUM(CASEWHEN("DEPTNO"=30)THEN"SAL"END)[22]2-"JOB"[VARCHAR2,9],"SAL"[NUMBER,22],"COMM"[NUMBER,22],"DEPTNO"[NUMBER,22]57rows selected
通过上面PLAN可以看到,PIVOT被转换成了如下语句：
SUM(CASEWHEN("DEPTNO"=10)THEN"SAL"END)[22],SUM(CASEWHEN("DEPTNO"=20)THEN"SAL"END)[22],SUM(CASEWHEN("DEPTNO"=30)THEN"SAL"END)[22]
也就是说,PIVOT只是写法简单了一些,实际上仍用的是CASE WHEN语句。
二、列转行
测试数据如下：
droptable test purge;createtable test asselect*from(select deptno, sal from emp)pivot(count(*)as ct,sum(sal)as s
for deptno in(10as d10,20as d20,30as d30));SQL>select*from test;
D10_CT D10_S D20_CT D20_S D30_CT D30_S
---------- ---------- ---------- ---------- ---------- ----------387505787569400SQL>
要求把三个部门的"人次"转为一列显示。以前这种需求一直用UNION ALL来写：
SQL>SELECT'10'AS 部门编码,d10_ct AS 人次 FROM test UNIONALL2SELECT'20'AS 部门编码,d20_ct AS 人次 FROM test UNIONALL3SELECT'30'AS 部门编码,d30_ct AS 人次 FROM test
4;
部门编码 人次
---------- ----------103205306
这时PLAN如下：
PlanHashValue : 2176849128---------------------------------------------------------------------| Id | Operation | Name |Rows| Bytes | Cost |Time|---------------------------------------------------------------------|0|SELECT STATEMENT ||3|39|9|00:00:01||1|UNION-ALL|||||||2|TABLE ACCESS FULL| TEST |1|13|3|00:00:01||3|TABLE ACCESS FULL| TEST |1|13|3|00:00:01||4|TABLE ACCESS FULL| TEST |1|13|3|00:00:01|---------------------------------------------------------------------
Note
------ dynamic sampling used for this statement
需要扫描test三次,而且如果列数较多,这种查询编写与维护都比较麻烦,而用UNPIVOT就不一样了。
SQL>SQL>select*from test unpivot(2 ct for deptno in(d10_ct,d20_ct,d30_ct)3);
D10_S D20_S D30_S DEPTNO CT
---------- ---------- ---------- ------ ----------875078759400 D10_CT 3875078759400 D20_CT 5875078759400 D30_CT 6SQL>
UNPIYOT函数生成两个新列：“deptno"与"ct”。
而in()中的D10_CT、D20_CT和D30_CT三列,其列名成为行"deptno"的值，原来D10_CT等列中的值分别转为"ct"列中的三行：
那怎么恢复成原来deptno号的样子?只要将语句整理如下：
SQL>select deptno AS lm,substr(deptno,-5,2)AS deptno,ct
2from test unpivot(3 ct for deptno in(d10_ct,d20_ct,d30_ct)4);
LM DEPTNO CT
------ -------- ----------
D10_CT 103
D20_CT 205
D30_CT 306
这时PLAN如下：
PlanHashValue : 734873962-----------------------------------------------------------------------| Id | Operation | Name |Rows| Bytes | Cost |Time|-----------------------------------------------------------------------|0|SELECT STATEMENT ||3|63|9|00:00:01||*1|VIEW||3|63|9|00:00:01||2|UNPIVOT|||||||3|TABLE ACCESS FULL| TEST |1|39|3|00:00:01|-----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------*1- filter("unpivot_view_006"."CT"ISNOTNULL)
Note
------ dynamic sampling used for this statement
可以看到,与PIVOT不一样,UNPIVOT不仅语句简略,而且只需要扫描test一次。我们可以很容易地在后面的unpivot列表里维护要转换的列。当然,UNPIVOT同样有限制。
如果现在有这么一个需求：还是用前面的表test：
SQL>select*from test;
D10_CT D10_S D20_CT D20_S D30_CT D30_S
---------- ---------- ---------- ---------- ---------- ----------387505787569400
我们要求将人次和sal聚合的结果恢复成按照部门统计的行列表，也就是下面这样的：
DEPTNO RC SM
-------- ---------- ----------103875020578753069400
那我们该怎么实现？
要记得如果同时有人次与工资合计要转换,unpivot就不能一次性完成,只有分别转换后再用JOIN连接。
select*from(select substr(deptno,1,3)as lm,
substr(deptno,-5,2)AS deptno,
rc
from(select*from test unpivot include nulls(rc for deptno in(d10_ct, d20_ct, d30_ct)))) a
innerjoin(select substr(deptno,1,3)as lm,
substr(deptno,-4,2)AS deptno,
sm
from(select*from test unpivot include nulls(sm for deptno in(d10_s,
d20_s,
d30_s)))) b
on a.lm = b.lm
LM DEPTNO RC LM DEPTNO SM
------------ -------- ---------- ------------ -------- ----------
D10 103 D10 108750
D20 205 D20 207875
D30 306 D30 309400
上面的结果只要去掉几个列名就实现了前面的要求。这里为了让两个结果集一致,使用了参数
include nulls
这样即使数据为空,也显示一行。
是否有办法只用UNPIVOT,而不用JOIN呢？看下面的示例：
SQL>select*2from test
3unpivot include nulls(rc for deptno in(d10_ct as10, d20_ct as20, d30_ct as30))4unpivot include nulls(sm for deptno2 in(d10_s as10, d20_s as20, d30_s as30));
DEPTNO RC DEPTNO2 SM
---------- ---------- ---------- ----------1031087501032078751033094002051087502052078752053094003061087503062078753063094009rows selected
可以看到,当有两个UNPJVOT时,生成的结果是一个笛卡儿积。
上面的语句实际上就是一个嵌套语句,前一个UNPIVOT结果出来后,再执行另一个
SQL>with t as(2select*3from test
4unpivot include nulls(rc for deptno in(d10_ct as10, d20_ct as20, d30_ct as30))5)6select*from t unpivot include nulls(sm for deptno2 in(d10_s as10, d20_s as20, d30_s as30));
DEPTNO RC DEPTNO2 SM
---------- ---------- ---------- ----------1031087501032078751033094002051087502052078752053094003061087503062078753063094009rows selected
回来继续说，那上面这样的数据就不能用了吗？必须用join吗？
不不不，既然他笛卡尔积了，其实针对需要的数据,在上面的查询上加一个过滤即可。
SQL>select*2from test
3unpivot include nulls(rc for deptno in(d10_ct as10, d20_ct as20, d30_ct as30))4unpivot include nulls(sm for deptno2 in(d10_s as10, d20_s as20, d30_s as30))5where deptno=deptno2;
DEPTNO RC DEPTNO2 SM
---------- ---------- ---------- ----------103108750205207875306309400SQL>
三、将结果集反向转置为一列
有时会要求数据竖向显示,如CLARK的数据显示如下（各行之间用空格隔开）：
CLARK
MANAGER
2450
我们使用刚学到的UNPIVOT,再加一点小技巧就可以。
select emps from(select ename, job, to_char(sal)as sal,nullas t_col
from emp
where deptno =10)unpivot include nulls(emps for aa in(ename,job,sal,t_col));
EMPS
------
CLARK
MANAGER
2450
KING
PRESIDENT
5000
sdf
sdf
1300
这里要注意以下两点。
- 与UNION ALL一样,要合并的几列数据类型必须相同,如果sal不用to_char转换,就会报错：
SQL>SQL>select emps from(2select ename, job, sal as sal,nullas t_col
3from emp
4where deptno =10)5unpivot include nulls(emps for aa in(ename,job,sal,t_col));select emps from(select ename, job, sal as sal,nullas t_col
from emp
where deptno =10)unpivot include nulls(emps for aa in(ename,job,sal,t_col))
ORA-01790: expression must have same datatype as corresponding expression
- 如果不加include nu11s,将不会显示空行：
select emps from(select ename, job, to_Char(sal)as sal,nullas t_col
from emp
where deptno =10)unpivot(emps for aa in(ename,job,sal,t_col));
EMPS
------
CLARK
MANAGER
2450
KING
PRESIDENT
5000
sdf
sdf
1300
总结
本篇文章讲解的主要内容是：目前Oracle支持的行列互换有两种方式：case when、pivot\unpivot,我将通过几个案例来给大家详解如何通过这两种方式实现“行转列”，“列转行”的需求，并通过执行计划看case when、pivot\unpivot二者的底层逻辑关系以及效率上的影响。
版权归原作者 赵延东的一亩三分地 所有， 如有侵权，请联系我们删除。