oracle 大表sum decode,Oracle 几种行转列的方式 sum+decode sum+case when pivot

原始数据：

方式一：

select t_name,

sum(decode(t_item, 'item1', t_num, 0)) item1,

sum(decode(t_item, 'item2', t_num, 0)) item2,

sum(decode(t_item, 'item3', t_num, 0)) item3,

sum(t_num) total

from test

group by t_name;

方式二：

select t_name,

sum(case

when t_item = 'item1' then

t_num

else

0

end) item1,

sum(case

when t_item = 'item2' then

t_num

else

0

end) item2,

sum(case

when t_item = 'item3' then

t_num

else

0

end) item3,

sum(t_num) total

from test

group by t_name;

方式三：

select t.*, (nvl(t.item1, 0) + nvl(t.item2, 0) + nvl(t.item3, 0)) as total

from (select *

from test pivot(sum(t_num) for t_item in('item1' as item1,

'item2' as item2,

'item3' as item3))) t;

unpivot的使用：

select t_name, t_item, t_num

from (

select *

from test pivot(sum(t_num) for t_item in('item1' as item1,

'item2' as item2,

'item3' as item3))

) unpivot(t_num for t_item in(item1,item2,item3));