目录
原始数据：
方式一：
方式二：
方式三：
unpivot的使用：
原始数据：
方式一：
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));