create table t (id int ,name varchar(8),value int)
insert into t
select 1,'a',3 union all
select 1,'b',4 union all
select 2,'b',6 union all
select 3,'a',8

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([name])+'=sum(case when [name]='+quotename([name],'''')+' then [value] else 0 end)'
from t group by[name]
exec('select [id]'+@s+' from t group by [id]')
-------------------------------------
id          a           b
----------- ----------- -----------
1           3           4
2           0           6
3           8           0

(3 行受影响)

------解决方案--------------------
动态行列转换
SQL code
declare @names varchar(max)
select @names = isnull(@name,'')+',sum(case when name = ''' +name + ''' then value else 0 end) as [' + name + ']'
from tab
group by name 

exec('
select id' + @names + '
from tab
group by id
')

------解决方案--------------------
参照
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html