1 create table AllScore( 2  3 id int identity(1,1) primary key,--主键 4 stu_name varchar(20),--学生名 5 category varchar(20),--学科 6 record float,--成绩 7 ) 8 /*插入数据*/ 9 insert into AllScore(stu_name,category,record)values('刘德华','Chinese',100)10 insert into AllScore(stu_name,category,record)values('刘德华','English',95)11 insert into AllScore(stu_name,category,record)values('刘德华','Mathmatics',98)12 13 insert into AllScore(stu_name,category,record)values('施瓦辛格','Chinese',90)14 insert into AllScore(stu_name,category,record)values('施瓦辛格','English',95)15 insert into AllScore(stu_name,category,record)values('施瓦辛格','Mathmatics',14)16 17 insert into AllScore(stu_name,category,record)values('太上老君','Chinese',70)18 insert into AllScore(stu_name,category,record)values('太上老君','English',95)19 insert into AllScore(stu_name,category,record)values('太上老君','Mathmatics',57)20 21 insert into AllScore(stu_name,category,record)values('毕达哥拉斯','Chinese',60)22 insert into AllScore(stu_name,category,record)values('毕达哥拉斯','English',95)23 insert into AllScore(stu_name,category,record)values('毕达哥拉斯','Mathmatics',68)24 25 insert into AllScore(stu_name,category,record)values('柏拉图','Chinese',60)26 insert into AllScore(stu_name,category,record)values('柏拉图','English',95)27 insert into AllScore(stu_name,category,record)values('柏拉图','Mathmatics',78)28 29 insert into AllScore(stu_name,category,record)values('亚里士多德','Chinese',40)30 insert into AllScore(stu_name,category,record)values('亚里士多德','English',22)31 insert into AllScore(stu_name,category,record)values('亚里士多德','Mathmatics',25)32 33 insert into AllScore(stu_name,category,record)values('卢梭','Chinese',40)34 insert into AllScore(stu_name,category,record)values('卢梭','English',50)35 insert into AllScore(stu_name,category,record)values('卢梭','Mathmatics',78)36 37 insert into AllScore(stu_name,category,record)values('老庄','Chinese',100)38 insert into AllScore(stu_name,category,record)values('老庄','English',20)39 insert into AllScore(stu_name,category,record)values('老庄','Mathmatics',98)40 41 -----开始行转列-------42 declare @sql nvarchar(4000)43 set @sql='Select stu_name '44 Select @sql=@sql+',sum(case when category='''+category+ ''' then Record else 0 end) As '''+category+''''45 From AllScore Group By category46 set @sql=@sql+' From AllScore Group By stu_name'47 Print @sql48 execute sp_executesql @sql

1 select *from test 2 insert into test(score,name,subject) values(80,'李四','语文'); 3 insert into test(score,name,subject) values(60,'李四','数学'); 4 insert into test(score,name,subject) values(90,'李四','英语'); 5 insert into test(score,name,subject) values(80,'张三','语文'); 6 insert into test(score,name,subject) values(60,'张三','数学'); 7 insert into test(score,name,subject) values(90,'张三','英语'); 8  9 -----------------------------------------------10 11 select max(name) as 姓名, 12     max(case13            when subject ='语文' then score14         end )as 语文 ,15     max(case16            when subject ='数学' then score17         end )as 数学 ,18     max(case19            when subject ='英语' then score20         end )as 英语 21 from test group by name

 

--方法一：select distinct name as '姓名' ,(select score from test where name=t.name and subject='语文') as '语文' ,(select score from test where name=t.name and subject='数学') as '数学',(select score from test where name=t.name and subject='英语') as '英语'from test as t--方法二：select max(name) as 姓名,     max(case           when subject ='语文' then score        end )as 语文 ,    max(case           when subject ='数学' then score        end )as 数学 ,    max(case           when subject ='英语' then score        end )as 英语 from test group by name----方法三：declare @sql nvarchar(max);set @sql='select name as 姓名'select @sql=@sql+',max(case when subject= '''+subject+''' then score end) as '+subject from test group by subjectselect @sql=@sql+' from test group by name'print @sqlexec sp_executesql @sqlselect *from test