让查询数据由行变成列

有这样两个数据表
(WuZi_Table)
物资编码(id), 颜色编号(Color_id), 数量(acount)
1111          1                   10
2222          2                   20
1111          1                   20
3333          3                   5
1111          3                   10
(YanSe_Table)
颜色编号(Color_id), 颜色名称(Color)
1                   红色
2                   兰色
3                   绿色
...
颜色可能会增加

如何查询得到
物资编码   红色    兰色    绿色  ...
1111       30      0       10
2222       0       20      0
3333       0       0       5

--建表

create table WuZi_Table(
 id int,Color_id varchar(2) ,acount int)

create table YanSe_Table(
Color_id varchar(2), Color varchar(4))

INSERT INTO [WuZi_Table]([id],[Color_id],[acount])
     VALUES(1111,1,10)
INSERT INTO [WuZi_Table]([id],[Color_id],[acount])
     VALUES(2222,2,20)
INSERT INTO [WuZi_Table]([id],[Color_id],[acount])
     VALUES(1111,1,20)
INSERT INTO [WuZi_Table]([id],[Color_id],[acount])
     VALUES(3333,3,15)
INSERT INTO [WuZi_Table]([id],[Color_id],[acount])
     VALUES(1111,3,10)

INSERT INTO [YanSe_Table]([Color_id],[Color])
     VALUES(1,'红色')
INSERT INTO [YanSe_Table]([Color_id],[Color])
     VALUES(2,'兰色')
INSERT INTO [YanSe_Table]([Color_id],[Color])
     VALUES(3,'蓝色')

--如果数据不多的话可以用下面的语句

SELECT ID,sum(CASE WHEN Color='红色' THEN aCount ELSE 0 END) AS 红色,  
           SUM(CASE WHEN Color='兰色' THEN aCount ELSE 0 END) AS 兰色,  
           SUM(CASE WHEN Color='绿色' THEN aCount ELSE 0 END) AS 绿色      
 from  WuZi_Table w ,YanSe_Table y where w.color_id=y.color_id  
 GROUP BY ID
GO
=====================================
--如果数据是动态生成的可用下面语句

declare @s varchar(8000)
set @s=''
select @s=@s+',['+color+']=sum(case color_id when '+color_id +' then acount else 0 end) '+char(10) from yanse_table
exec ('select distinct id '+@s+' from wuzi_table group by id')

--print @s

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝