with a as
(select 'dev1' col1,'file1' col2,'5' col3 from dual
union all
select 'dev1' col1,'file2' col2,'6' col3 from dual
union all
select 'dev2' col1,'file2' col2,'3' col3 from dual
union all
select 'dev2' col1,'file3' col2,'4' col3 from dual
)
目标结果：
列名：file1 file2 file3 ... .... ...合计
dev1 5 6 11
dev2 3 4 7
合计 5 9 4 18
------解决方案--------------------
- SQL code
select col1,file1,file2,file3,file1+file2+file3 合计from (select nvl(col1,'合计') col1, sum(decode(col2,'file1',col3,0)) file1, sum(decode(col2,'file2',col3,0)) file2, sum(decode(col2,'file3',col3,0)) file3 from a group by rollup(col1) order by col1) col1 file1 file2 file3 合计----------------------------------------------1 dev1 5 6 0 112 dev2 0 3 4 73 合计 5 9 4 18