sql动态行转列

来源：互联网发布：受人冷落的网络词语编辑：程序博客网时间：2024/06/07 03:34

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 tbgroup 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 aset @sql = @sql + ' from tb group by username'exec(@sql)

0 0