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(num