Oracle 行转列和列转行

最新推荐文章于 2024-07-31 14:31:55 发布

惟是致良知

最新推荐文章于 2024-07-31 14:31:55 发布

阅读量2.1w

点赞数 2

分类专栏： SQL 文章标签： pivot WM_CONCAT 行转列去重

本文链接：https://blog.csdn.net/hustzw07/article/details/51426935

版权

本文介绍了在Oracle数据库中如何进行行转列和列转行的操作。通过DECODE、CASE和PIVOT函数实现数据转换，同时讲解了多列求和、WM_CONCAT去重功能，并探讨了动态列名的挑战。此外，还展示了UNPRIVOT操作将数据从列转行的过程及其对空值的处理方式。

摘要由CSDN通过智能技术生成

场景

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

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