Oracle 行转列和列转行

场景

在生成报表的时候，很多时候对某列取值固定或者有限的几个值时，进行转列分析。如下：

facility列取值只有四种，每种一列，遇到相同facility求和。如下图：

实现方式

DECODE

利用DECODE函数、聚合函数SUM、GROUP BY分组实现。

WITH CO_ORDER AS(    
  SELECT 'DOM1' Customer, 'ZHA01' facility , 4000 TEU FROM dual UNION ALL    
  SELECT 'DOM1' Customer, 'ZHA01' facility , 5000 TEU FROM dual UNION ALL    
  SELECT 'DOM1' Customer, 'ZHA02' facility , 9000 TEU FROM dual UNION ALL   
  SELECT 'DOM1' Customer, 'ZHA03' facility , 9000 TEU FROM dual UNION ALL  
  SELECT 'DOM1' Customer, 'ZHA04' facility , 4000 TEU FROM dual UNION ALL  
  SELECT 'DOM2' Customer, 'ZHA01' facility , 6500 TEU FROM dual UNION ALL    
  SELECT 'DOM2' Customer, 'ZHA02' facility , 6000 TEU FROM dual UNION ALL    
  SELECT 'DOM2' Customer, 'ZHA03' facility , 5000 TEU FROM dual UNION ALL    
  SELECT 'DOM2' Customer, 'ZHA04' facility , 3000 TEU FROM dual     
)
SELECT t.Customer, SUM(decode(t.facility, 'ZHA01', t.teu)) AS ZHA01,
       SUM(decode(t.facility, 'ZHA02', t.teu)) AS ZHA02,
       SUM(decode(t.facility, 'ZHA03', t.teu)) AS ZHA03,
       SUM(decode(t.facility, 'ZHA04', t.teu)) AS ZHA04
  FROM CO_ORDER t
  GROUP BY t.Customer;

该方式简单直观，当然每列具体要标示出来，所以只能处理固定种类的，不易程序后期扩展。

case

case函数的写法相对比较简洁，如下

WITH CO_ORDER AS(      
  SELECT 'DOM1' Customer, 'ZHA01' facility , 4000 TEU FROM dual UNION ALL      
  SELECT 'DOM1' Customer, 'ZHA01' facility , 5000 TEU FROM dual UNION ALL      
  SELECT 'DOM1' Custome