Oracle：Pivot 和 Unpivot 转多列并包含多个名称

Pivot

1、准备数据

create table t_demo(id int,name varchar(20),nums int);  ---- 创建表  
insert into t_demo values(1, '苹果', 1000);  
insert into t_demo values(2, '苹果', 2000);  
insert into t_demo values(3, '苹果', 4000);  
insert into t_demo values(4, '橘子', 5000);  
insert into t_demo values(5, '橘子', 3000);  
insert into t_demo values(6, '葡萄', 3500);  
insert into t_demo values(7, '芒果', 4200);  
insert into t_demo values(8, '芒果', 5500); 

2、Pivot行转多列

select * 
from (select name, nums from t_demo) 
pivot (sum(nums) total,min(nums) min for name in ('苹果' apple, '橘子' orange, '葡萄' grape, '芒果' mango));

 

Unpivot

1、准备数据

CREATE TABLE t_demo_unpivot as
select * 
from (select name, nums from t_demo) 
pivot (sum(nums) total,min(nums) min for name in ('苹果'