Oracle Pivot学习心得

　　今天在做一个查询报表需要将多行的查询结果转换成一行，数据格式如下

　　ID　　　　　  Type　 Parameter Value 　　　　Machine_NO　　 Operator 　　UpdateTime

1 111111111111 DS 　　after　　   155.780000  　 　P2-2222 　    88888888　　 2014/6/5 11:52:13 
2 111111111111 DS 　　airbag　　 0.850000 　　     P2-2222　　   88888888　　 2014/6/5 11:52:40 
3 111111111111 DS 　　before 　　163.570000　     P1-1111　　   00000000　　 2014/6/5 11:30:05 
4 111111111111 DS 　   delta　　   22.990000 　  　 P2-2222　　   88888888　　 2014/6/5 11:52:25 
5 111111111111 IJ 　　 after　　    163.570000  　   P1-1111　　   00000000　　 2014/6/5 11:29:57 
6 111111111111 IJ 　　 before　　 133.640000　　   P1-1111　　   00000000　　 2014/6/5 11:21:51 
7 111111111111 IJ 　 　delta　　   29.9300001　　   P1-1111　　   00000000　　 2014/6/5 11:30:21 

　

需要将这7行数据转换成2行数据，将同一ID同一Type不同的parameter的Value值放到同一行中。

如下的结果

如果用拼接的方法来完成这个任务虽然能达到同样的效果，但是SQL非常长不易理解。而且SQL语句的执行效率很低很浪费数据库资源。

在ORACLE 中有一个函数（Pivot）可以很容易的达到这个效果

用法Select * From 表名
PIVOT
(
  SUM('要合并的列1'),MAX('要合并的列2'),....FOR 将值转换成列的列名 IN
(列值1，列值2，列值3，列值4....）
)

从用法中可以看出sum,max是配合PIVOT聚合函数，聚合函数必须要有。

 

实际用例：

select *
  from (

　　　　select ID,

　　　　　　　　Type,

　　　　　　　　parameter,

　　　　　　　　value,

　　　　　　　　machine_no,

　　　　　　　　Operator,

　　　　　　　　update_time
     　　     from tableName)

pivot(

　　　　max(value) as value,

　　　　 max(machine_no) as machine_no,

　　　　max(Operator) as Operator,

　　　　max(update_time) as update_time

　　 for parameter in(　　

　　　　　　　　　　　　'before' as Before,

　　　　　　　　　　　　'after' as After,

　　　　　　　　　　　　'delta' as Delta，

　　　　　　　　　　　　'airbag' as AirBag )
      
   　　　　　　　　　    )
 where ID= '111111111111'

 在转换过程中生成的新列名是由上面红色字组合成的，生成的新列数为4*4=16列。例：Before_Value Before_Machine_No,Before_Operator,

下图是执行计划