oracle查询中行转列、列转行以及PIVOT、UNPIVOT使用

行转列

行转列的大致意思是，将表中多行数据转成一行的多个字段输出。需要行转列的业务逻辑通常是，将表中的一个字段分类统计后作为多个结果字段输出。

 

一个简单例子：

有一个学生表，有姓名、学科、成绩三个字段

Java代码  

1. create table studentScores(  
2.        username varchar2(20),  
3.        subject  varchar2(30),  
4.        score    float  
5. )  
6. insert into studentScores values('justin','语文',90);  
7. insert into studentScores values('justin','英语',80);  
8. insert into studentScores values('justin','数学',90);  
9. insert into studentScores values('jia','语文',90);  
10. insert into studentScores values('jia','英语',80);  
11. insert into studentScores values('jia','数学',90);  

 业务场景是我需要将每个学生的各科成绩拉成一行，方便查看统计。大致效果如下图

 

 

通常都会用到GROUP BY 和 CASE WHEN

Java代码  

1. select username,  
2.        max(case subject when '语文' then score else 0 end) as 语文,  
3.        max(case subject when '数学' then score else 0 end) as 数学,  
4.        max(case subject when '英语' then score else 0 end) as 英语  
5.  from studentScores  
6.  group by username  

 

pivot函数

11g之后oracle提供了一个pivot函数来专门实现这个行转列功能，上面的sql语句可用下面语句代替

Java代码  

1. select username,"'语文'","'数学'","'英语'" //这里也能用*，用pivot函数查出来的字段名称就是‘语文’注意这两个单引号也是名称一部分，所以这行要用双引号括起来  
2.      from studentScores  
3.      pivot(max(score)   //聚合列取值  
4.                for subject in ('语文','数学','英语')) //定义哪个列的哪些值 转成新的列  以及新列的顺序  

  pivot函数有个缺点。使用GROUP BY + CASE WHEN写法时，查询结果行数就是 group by字段非重复值的数量。而pivot查询的结果相当于group by除聚合列和待行转列列 之外所有列的结果。比如为studentScores表添加一个teacher字段，输出不同值，还是上面的pivot查询语句，结果如下：

 

 还有试过不手动输入上面‘语文’等列，而是用SELECT DISTINCT SUBJECT FROM STUDENTSCORES子查询结果作为in的值，结果发现是不行的。所以抛开性能（不知道性能有没有提示）不谈，除了sql语句比较简洁外，这个函数没什么优越性

 

 

列转行

列转行更容易理解，将表中多个列（一般是同一纬度相关）每个列拉成一行数据。

比如有个项目供应量表，固定供应方有海外、本地、南方、北方四个，表如下：

Java代码  

1. create table projectDetail(  
2.  projectName varchar2(20),  
3.  overseaSupply int,  
4.  nativeSupply int,  
5.  southsupply int,  
6.  northsupply int);  
7.    
8.  insert into projectdetail values('A',100,100,100,100);  
9.  insert into projectdetail values('B',50,50,50,50);  
10.  insert into projectdetail values('C',200,200,200,200);  

 现在业务需求是展示项目名，供应方，供应量三个字段，需要展示结果如下：

 

 

 查询逻辑一般就是查询相关字段取统一别名，然后用union all将结果连接

Java代码  

1. select projectname,'overseasupply' as supplier,overseasupply as supplysum  
2.  from projectdetail  
3.  union all  
4.  select projectname,'nativeSupply' as supplier,nativeSupply as supplysum  
5.  from projectdetail  
6.  union all  
7.  select projectname,'southsupply' as supplier,southsupply as supplysum  
8.  from projectdetail  
9.  union all  
10.  select projectname,'northsupply' as supplier,northsupply as supplysum  
11.  from projectdetail  

 

unpivot

使用unpivot函数获得上面相同效果的sql语句如下

Java代码  

1. select projectname, supplier, supplysum   //与下面别名相同  
2.    from projectdetail unpivot(supplysum //为列转行中多列变成一列的 值取别名  
3.                                  for supplier    //在结果中成为一列的原多列取统一别名  
4.                                                    in(overseasupply,  
5.                                                         nativeSupply,  
6.                                                         southsupply,  
7.                                                         northsupply))