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
