Oracle行转列-列转行各种方式wm_concat函数、LISTAGG函数、PIVOT函数、UNPIVOT函数、非数字，以及自动动态获取要转换的列字段名（超详细）

最新推荐文章于 2025-02-14 09:13:02 发布

亚宝一枝花

最新推荐文章于 2025-02-14 09:13:02 发布

阅读量1.6k

点赞数 1

分类专栏： Oracle 文章标签：行转列列转行 wm_concat LISTAGG PIVOT UNPIVOT

本文链接：https://blog.youkuaiyun.com/Hjchidaozhe/article/details/100826200

版权

本文详细介绍了Oracle中行转列（包括wm_concat、PIVOT函数方式）和列转行（UNPIVOT函数方式）的各种方法。通过实例展示了如何使用这些函数和操作，包括动态获取列字段名的存储过程实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;

行转列（case when方式）：

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;

行转列（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'));

行转列（WM_CONCAT函数方式：）

WITH CO_ORDER AS(    
  SELECT 'DOM1' CUSTOMER, 'ZHA01&

最低0.47元/天解锁文章