求助，SQL查询语句

[kdboy]

table1:

ID Type Num
001 RMB 500
002 KHD 300
001 KHD 450
003 RMB 200
001 RMB 100
002 KHD 600

要求使用sql语句，查询出如下结果：

ID RMB KHD
001 600 450
002 0 900
003 200 0

高手帮帮忙，希望找一最简单的SQL语句。
谢谢了！

[xeqtr1982]

create table tb(ID varchar(10),Type varchar(10),Num int)
insert into tb select '001','RMB',500
union all select '002','KHD',300
union all select '001','KHD',450
union all select '003','RMB',200
union all select '001','RMB',100
union all select '002','KHD',600


declare @sql varchar(8000)
set @sql='select ID'
select @sql=@sql+',['+Type+']=sum(case Type when '''+Type+''' then Num else 0 end)' from tb group by Type order by type desc
exec(@sql+' from tb group by ID')

drop table tb

[xeqtr1982]

--如果type的个数固定，可以不用上面的动态sql
create table tb(ID varchar(10),Type varchar(10),Num int)
insert into tb select '001','RMB',500
union all select '002','KHD',300
union all select '001','KHD',450
union all select '003','RMB',200
union all select '001','RMB',100
union all select '002','KHD',600

select ID,
RMB=sum(case Type when 'RMB' then Num else 0 end),
KHD=sum(case Type when 'KHD' then Num else 0 end)
from tb
group by ID

[zengtang]

如果类型固定的话：
select a.id,rmb,hkd from (select id,sum(num) as rmb from table1 where type='rmb' group by id) a,
(select id,sum(num) as hkd from table1 where type='hkd' group by id) b
where a.id=b=id

[zengtang]

条件输错了
是 where a.id=b.id