MSSQL 行列转换

--普通版行列转换
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 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) 
go 
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b 
--下面这句也可以的
select * from tb pivot (max(分数) for 课程 in (语文,数学,物理)) b 

--进阶版行列转换
create table mytemp (id int,[name] varchar(10),math int,chinese int,english int)
insert into mytemp
select 1 id,'aaa' [name],'78' math,'88' chinese,'99' english union all
select 2 ,'bbb',3,5,76 union all
select 3,'ccc',6,7,8
go
select * into #temptest from (
select id,'name' kemu ,[name] fenshu from mytemp
union all 
select id,'math'  ,cast(math as varchar(10)) from mytemp
union all 
select id,'chinese'  ,cast(chinese as varchar(10)) from mytemp
union all 
select id,'english'  ,cast(english as varchar(10)) from mytemp
) tbtest
--select * from tb pivot (max(fenshu) for id in ([1],[2],[3])) b
declare @sql varchar(max)
select @sql = isnull(@sql + ',' , '') + '['+cast(id as varchar(10))+']' from mytemp
exec ('select * from #temptest pivot (max(fenshu) for id in ('+@sql+')) b')
drop table #temptest