sql动态行转列

create table tb(username varchar(10) , kecheng varchar(10) , fenshu int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
insert into tb values('张三' , '英语' , 60)
go

--静态
select username as 姓名 ,
  max(case kecheng when '语文' then fenshu else 0 end) 语文,
  max(case kecheng when '数学' then fenshu else 0 end) 数学,
  max(case kecheng when '物理' then fenshu else 0 end) 物理
from tb
group by username


--动态
declare @sql varchar(8000)
set @sql = 'select username '
select @sql = @sql + ' , max(case kecheng when ''' + kecheng + ''' then fenshu else 0 end) [' + kecheng + ']'
from (select distinct kecheng from tb) as a
set @sql = @sql + ' from tb group by username'
exec(@sql)