# Oracle 行转列和列转行

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

1. DECODE

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;  

2. 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' 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 Customer,
sum (CASE WHEN facility = 'ZHA01' THEN TEU ELSE 0 END) AS ZHA01,
sum (CASE WHEN facility = 'ZHA02' THEN TEU ELSE 0 END) AS ZHA02,
sum (CASE WHEN facility = 'ZHA03' THEN TEU ELSE 0 END) AS ZHA03,
sum (CASE WHEN facility = 'ZHA04' THEN TEU ELSE 0 END) AS ZHA04
FROM CO_ORDER GROUP BY Customer ORDER BY Customer ASC;  

3. PIVOT

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 * FROM (
SELECT t.Customer, t.facility, SUM(TEU) EU FROM CO_ORDER t
GROUP BY t.Customer, t.facility
) t pivot(SUM(t.EU) FOR facility IN ('ZHA01', 'ZHA02', 'ZHA03', 'ZHA04'));  

4. 多列求和

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 Customer,ZHA01,ZHA02,ZHA03,ZHA04,ZHA01+ZHA02+ZHA03 +ZHA04 as TOTAL from (
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);  

5. WM_CONCAT

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 Customer,WM_CONCAT(facility) ALL_FACILITY FROM CO_ORDER GROUP BY Customer;  

6. 去重

WITH tt AS (
SELECT 'DOM1' wb_id, 'ZHA01' reply ,  'ZHA01' addre ,4000 TEU FROM dual UNION ALL
SELECT 'DOM1' wb_id, 'ZHA02' reply ,  'ZHA01' addre ,9000 TEU FROM dual UNION ALL
SELECT 'DOM2' wb_id, 'ZHA01' reply ,  'ZHA01' addre ,9000 TEU FROM dual UNION ALL
SELECT 'DOM2' wb_id, 'ZHA04' reply ,  'ZHA01' addre ,4000 TEU FROM dual UNION ALL
)

-- 选出待删除的数据集合
SELECT *  FROM tt
;  

7.列转行

WITH CO_ORDER2 AS(
SELECT 'DOM1' Customer, 'office1'  ZHA02, 'office1' ZHA03, 'office2' ZHA04 FROM dual UNION ALL
SELECT 'DOM1' Customer, 'office2'  ZHA02, 'office3' ZHA03, 'office2' ZHA04 FROM dual UNION ALL
SELECT 'DOM2' Customer, 'office4'  ZHA02, 'office1' ZHA03, 'office2' ZHA04 FROM dual UNION ALL
SELECT 'DOM2' Customer, 'office3'  ZHA02, 'office1' ZHA03, 'office1' ZHA04 FROM dual
)
--SELECT * FROM CO_ORDER2
SELECT Customer, offcs, facility FROM CO_ORDER2 UNPIVOT (offcs FOR facility IN ( ZHA02, ZHA03, ZHA04) ); 

UNPRIVOT 帮我们生成了两个新列，一列用于存放 office1 之类的值，另一个新列存的是 这个数据来源于之前那个原始列名。

WITH CO_ORDER2 AS(
SELECT 'DOM1' Customer, 'office1'  ZHA02, 'office1' ZHA03, 'office2' ZHA04 FROM dual UNION ALL
SELECT 'DOM1' Customer, 'office2'  ZHA02, '' ZHA03, 'office2' ZHA04 FROM dual UNION ALL
SELECT 'DOM2' Customer, 'office4'  ZHA02, 'office1' ZHA03, 'office2' ZHA04 FROM dual UNION ALL
SELECT 'DOM2' Customer,   ZHA02, 'office1' ZHA03, 'office1' ZHA04 FROM dual
)
--SELECT * FROM CO_ORDER2
SELECT Customer, offcs, facility FROM CO_ORDER2 UNPIVOT (offcs FOR facility IN ( ZHA02, ZHA03, ZHA04) );  

• 广告
• 抄袭
• 版权
• 政治
• 色情
• 无意义
• 其他

120