sql2005 多行转列

if exists(select *from tempdb.dbo.sysobjects where id=object_id(N'tempdb.dbo.#tab') and type='u')

drop table #tab

create table #tab ([name] nvarchar(20),subject nvarchar(20),point int)
insert into #tab
select N'小明', N'数学',60
union
select N'小明', N'语文',80
union
select N'小明', N'英语',90
union
select N'小华', N'数学',65
union
select N'小华', N'语文',85
union
select N'小华', N'英语',95

declare @sqlStart nvarchar(2000)='select name'
declare @sqlEnd nvarchar(200)=' from #tab group by name'

select  @sqlStart= @sqlStart +',sum(case subject when N'''+subject+''' then point else 0 end) as N'''+subject+'''' from (select distinct subject from #tab) a

select @sqlStart+@sqlEnd
exec( @sqlStart+@sqlEnd )