ORACLE实现表的反转

这是目标数据：

[img]http://dl.iteye.com/upload/attachment/377431/573eb4a1-b4b6-38c5-8cee-3431d845f821.jpg[/img]

这是想得到的结果：

[img]http://dl.iteye.com/upload/attachment/377435/d05f7252-8731-342d-a125-e327fca585bf.png[/img]


下面开始语句部分：
1. 建表：

create table test(name varchar2(20),dept varchar2(20),km varchar2(100),sal number(20,2));

2.插入数据：

insert into test values('贵州','软件实施部','差旅费',154);
insert into test values('贵州','软件实施部','差旅费',180);
insert into test values('贵州','软件实施部','市内交通费',5);
insert into test values('贵州','软件实施部','市内交通费',5);
insert into test values('贵州','软件实施部','差旅费',82);
insert into test values('贵州','软件实施部','差旅费',85);
insert into test values('贵州','人力资源中心','员工借款',-500);
insert into test values('贵州','财务中心','押金',1000);
insert into test values('贵州','财务中心','资金调拨款',100000);
insert into test values('贵州','财务中心','财务费用',25);
insert into test values('贵州','财务中心','资金调拨款',50000);
insert into test values('贵州','财务中心','财务费用',25);
insert into test values('贵州','财务中心','融资借款',-50000);
insert into test values('贵州','营销中心','员工借款',10000);
insert into test values('贵州','营销中心','员工借款',35000);
insert into test values('贵州','售后服务部','员工借款',1500);
insert into test values('贵州','财务中心','财务费用',50);
insert into test values('贵州','财务中心','财务费用',100000);
insert into test values('贵州','财务中心','财务费用',149000);
insert into test values('贵州','财务中心','财务费用',25);
insert into test values('贵州','系统部','员工借款',1500);
insert into test values('贵州','营销中心','员工借款',2000);
insert into test values('贵州','人力资源中心','员工借款',-3596);
insert into test values('北京','人力资源中心','房租物业水电',800);
insert into test values('北京','人力资源中心','固定资产及软件添置',190);
insert into test values('北京','人力资源中心','固定资产及软件添置',25);
insert into test values('贵州','售后服务部','零星采购',20);
insert into test values('北京','测试部','差旅费',100);

3.实现反转：

select
  distinct
	a.name,a.dept,
  nvl(sum(b.sal),0) "财务费用",
  nvl(sum(c.sal),0) "差旅费",
   nvl(sum(d.sal),0) "房租物业水电",
  nvl(sum(e.sal),0) "固定资产及软件添置",
   nvl(sum(f.sal),0) "零星采购",
  nvl(sum(g.sal),0) "融资借款",
   nvl(sum(h.sal),0) "市内交通费",
  nvl(sum(i.sal),0) "押金",
  nvl(sum(j.sal),0) "员工借款",
  nvl(sum(k.sal),0) "资金调拨款"
from test a
left outer join test b on b.name=a.name and b.dept=a.dept and b.km='财务费用'
left outer join test c on c.name=a.name and c.dept=a.dept and c.km='差旅费'
left outer join test d on d.name=a.name and d.dept=a.dept and d.km='房租物业水电'
left outer join test e on e.name=a.name and e.dept=a.dept and e.km='固定资产及软件添置'
left outer join test f on f.name=a.name and f.dept=a.dept and f.km='零星采购'
left outer join test g on g.name=a.name and g.dept=a.dept and g.km='融资借款'
left outer join test h on h.name=a.name and h.dept=a.dept and h.km='市内交通费'
left outer join test i on i.name=a.name and i.dept=a.dept and h.km='押金'
left outer join test j on j.name=a.name and j.dept=a.dept and h.km='员工借款'
left outer join test k on k.name=a.name and k.dept=a.dept and k.km='资金调拨款'
group by a.name,a.dept
order by a.name,a.dept