oracle列传行函数,oracle行转列和列转行（pivot 和 unpivot 函数，wm_concat函数 ）

create table demo(id int,name varchar(20),nums int); ---- 创建表

insert into demo values(1, '苹果', 1000);

insert into demo values(2, '苹果', 2000);

insert into demo values(3, '苹果', 4000);

insert into demo values(4, '橘子', 5000);

insert into demo values(5, '橘子', 3000);

insert into demo values(6, '葡萄', 3500);

insert into demo values(7, '芒果', 4200);

insert into demo values(8, '芒果', 5500);

---固定行转列

select * from

(select name,nums from demo) pivot (sum(nums) for name in ('苹果', '橘子', '葡萄', '芒果'));

---动态行转列

select * from (

select stat_time,target_code,sum(target_value) over(partition by stat_time order by target_code) as target_value from (

select stat_time,target_code,sum(target_value) as target_value from user_data_daily

where target_type='100001' and appkey='1400140930701' and stat_time>=date'2017-04-01' and stat_time

group by stat_time,target_code

) order by stat_time,target_code

)pivot xml (

sum(target_value) for target_code in (any)

);