动态竖表转横表

实现动态的竖表转横表,以下代码在SQL Server中测试通过:

 

if exists(select name from sysobjects where name='test')
drop table test
go
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)

declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) '+km+''
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)