欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle的行列转换

程序员文章站 2022-04-16 18:41:52
...

首先准备如下表格 tony@ORA11GR2gt; select empno,ename,job,sal,deptno from emp 2 order by deptno,job; EM

首先准备如下表格

现在查询各部门各工种的总薪水,

但是这样不直观,如果能够把每个工种作为1列显示就会更一目了然.
这就是需要行转列。
在11g之前,需要一点技巧,利用decode函数才能完成这个目标。

得到结果:

如果要在变回前面的结果,需要用到笛卡尔乘积,一行变五行,然后利用decode。例如:

得到结果:

11g之后,Oracle增加了pivot和unpivot语句,可以很方便的完成这个转换。
pivot
先来看看pivot的语法是 来看个例子:

得到结果:

实际上,oracle对pivot子句中出现的列以外的列做了一个隐式的group by.
现在,如果想要再结果中增加1列,显示部门的薪水总合,,可以这么做,

2点说明,
1)oracle对pivot子句中出现的列以外的列,也就是deptno和SAL_TOTAL做了隐式的group by.
这里用了分析函数,对于每个deptno,SAL_TOTAL是唯一的,所以group by的结果还是3行。
2)oracle会拼接列名 = for字句中别名+聚合函数别名,比如'PRESIDENT'+'_'+'SAL_TOTAL'。

可以指定多个聚合函数,例如统计薪水总合和人数总合:

for子句可以指定多列,
为此,先给emp表追加1列rank,取值为'A','B',

现在,想统计SALESMAN和CLERK的员工中,rank A和rank B各自的人数。

unpivot
现在来看看unpivot的用法,
unpivot的语法: 例如,有下面的的表格,

用unpivot语句来做列到行的转换,

如果加上include nulls子句

可以指定多个pivot-column,例如对于下面的表格

转换为行以后,有2列数据分别显示薪水总合和人数总和。也就是需要对2列同时进行转换。

关于ANY子句

pivot的一个不便之处是需要在IN子句中指定所有取值,能不能自动完成呢?
比如for job in (select distinct(job) from emp),可惜oracle不支持这么做。
不过oracle支持通过这种方式返回XML格式数据,例如,

*可以把上面的ANY换成select distinct(job) from emp

Oracle的行列转换