Oracle pivot 行转列、unpivo列转行函数使用详解

快来打我* 2022-05-22 02:52 241阅读 0赞

**一 pivot函数表达式：**  
有一个职工部门工资表pivot\_tmp，数据如下:  
![图一][70]  
如果我们想要求各部门的工资和，很简单一个sum和group by 就满足了。

select t.dept_name 部门,sum(t.salary) 工资总额 from pivot_tmp t group by t.dept_name;

执行结果:  
![这里写图片描述][70 1]  
但是如果我们要求结果显示如下呢：  
![图二][70 2]  
可以先尝试用decode 函数写，代码如下，

select '工资总额' 部门, sum(decode(t.dept_name, '销售部', t.salary)) 销售部, sum(decode(t.dept_name, '人事', t.salary)) 人事, sum(decode(t.dept_name, '财务', t.salary)) 财务, sum(decode(t.dept_name, '总经理室', t.salary)) 总经理室 from pivot_tmp t;

用pivot函数下代码如下：

select '工资总额' 部门, pn.* from (select t.dept_name, t.salary from pivot_tmp t) pivot(sum(salary) for dept_name in('销售部' 销售部, '人事' 人事,'财务' 财务, '总经理室' 总经理室)) pn;

**二、unpivot函数**  
现有部门个季度销售业绩表unpivot\_tmp ，数据如下：  
![这里写图片描述][70 3]  
现需从表中获得如下查询结果:  
![这里写图片描述][70 4]  
这里同样可以考虑用无数个union all

select t.dept_name 部门,'QUARTER_1' 季度, t.quarter_1 业绩 from unpivot_tmp t where t.dept_name='销售一部' union all select t.dept_name 部门,'QUARTER_2' 季度, t.quarter_2 业绩 from unpivot_tmp t where t.dept_name='销售一部' union all select t.dept_name 部门,'QUARTER_3' 季度, t.quarter_3 业绩 from unpivot_tmp t where t.dept_name='销售一部' union all select t.dept_name 部门,'QUARTER_4' 季度, t.quarter_4 业绩 from unpivot_tmp t where t.dept_name='销售一部' union all select t.dept_name 部门,'QUARTER_1' 季度, t.quarter_1 业绩 from unpivot_tmp t where t.dept_name='销售二部' union all select t.dept_name 部门,'QUARTER_2' 季度, t.quarter_2 业绩 from unpivot_tmp t where t.dept_name='销售二部' union all select t.dept_name 部门,'QUARTER_3' 季度, t.quarter_3 业绩 from unpivot_tmp t where t.dept_name='销售二部' union all select t.dept_name 部门,'QUARTER_4' 季度, t.quarter_4 业绩 from unpivot_tmp t where t.dept_name='销售二部' union all select t.dept_name 部门,'QUARTER_1' 季度, t.quarter_1 业绩 from unpivot_tmp t where t.dept_name='销售三部' union all select t.dept_name 部门,'QUARTER_2' 季度, t.quarter_2 业绩 from unpivot_tmp t where t.dept_name='销售三部' union all select t.dept_name 部门,'QUARTER_3' 季度, t.quarter_3 业绩 from unpivot_tmp t where t.dept_name='销售三部' union all select t.dept_name 部门,'QUARTER_4' 季度, t.quarter_4 业绩 from unpivot_tmp t where t.dept_name='销售三部' ;

如果用unpivot的话就非常简单了

select dept_name 部门, 季度, 业绩
      from unpivot_tmp unpivot(业绩 for 季度 in(QUARTER_1,
                                            QUARTER_2,
                                            QUARTER_3,
                                            QUARTER_4));
    //注意这里的季度 和业绩是临时变量，并不是表 unpivot_tmp 的字段。

注意事项 在pivot中的 in表达式里面的字段一定要用单引号,而unpivot中in表达式里面的字段则不需要

[70]: /images/20220522/6365f8c221fc41c49f848faae151efb3.png
[70 1]: /images/20220522/0dbf6415b8a04c2d9b7e5f49f0622835.png
[70 2]: /images/20220522/2f33111e26394225a4889893643a25db.png
[70 3]: /images/20220522/b725a63dacc24045b2b16d60aeb2bc5e.png
[70 4]: /images/20220522/0d2452a78e3f4b5faa62c4454d01d930.png