场景
在生成报表的时候,很多时候对某列取值固定或者有限的几个值时,进行转列分析。如下:
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