SQLServer 实现行转列

create table AllScore(

id int identity(1,1) primary key,--主键
stu_name varchar(20),--学生名
category varchar(20),--学科
record float,--成绩
)
/*插入数据*/
insert into AllScore(stu_name,category,record)values('刘德华','Chinese',100)
insert into AllScore(stu_name,category,record)values('刘德华','English',95)
insert into AllScore(stu_name,category,record)values('刘德华','Mathmatics',98)

insert into AllScore(stu_name,category,record)values('施瓦辛格','Chinese',90)
insert into AllScore(stu_name,category,record)values('施瓦辛格','English',95)
insert into AllScore(stu_name,category,record)values('施瓦辛格','Mathmatics',14)

insert into AllScore(stu_name,category,record)values('太上老君','Chinese',70)
insert into AllScore(stu_name,category,record)values('太上老君','English',95)
insert into AllScore(stu_name,category,record)values('太上老君','Mathmatics',57)

insert into AllScore(stu_name,category,record)values('毕达哥拉斯','Chinese',60)
insert into AllScore(stu_name,category,record)values('毕达哥拉斯','English',95)
insert into AllScore(stu_name,category,record)values('毕达哥拉斯','Mathmatics',68)

insert into AllScore(stu_name,category,record)values('柏拉图','Chinese',60)
insert into AllScore(stu_name,category,record)values('柏拉图','English',95)
insert into AllScore(stu_name,category,record)values('柏拉图','Mathmatics',78)

insert into AllScore(stu_name,category,record)values('亚里士多德','Chinese',40)
insert into AllScore(stu_name,category,record)values('亚里士多德','English',22)
insert into AllScore(stu_name,category,record)values('亚里士多德','Mathmatics',25)

insert into AllScore(stu_name,category,record)values('卢梭','Chinese',40)
insert into AllScore(stu_name,category,record)values('卢梭','English',50)
insert into AllScore(stu_name,category,record)values('卢梭','Mathmatics',78)

insert into AllScore(stu_name,category,record)values('老庄','Chinese',100)
insert into AllScore(stu_name,category,record)values('老庄','English',20)
insert into AllScore(stu_name,category,record)values('老庄','Mathmatics',98)

-----开始行转列-------
declare @sql nvarchar(4000)
set @sql='Select stu_name '
Select @sql=@sql+',sum(case when category='''+category+ ''' then Record else 0 end) As '''+category+''''
From AllScore Group By category
set @sql=@sql+' From AllScore Group By stu_name'
Print @sql
execute sp_executesql @sql