新手，求教一个统计的有关问题!望，多谢

新手，求教一个统计的问题!望高手指点，谢谢！
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