--create temp table
create table #Grade(
Name varchar(20),
SubjectName varchar(20),
Grade int
)
--insert data
insert into #Grade(Name,SubjectName,Grade) values(‘jimmy‘,‘Math‘,100)
insert into #Grade(Name,SubjectName,Grade) values(‘jimmy‘,‘Chinese‘,110)
insert into #Grade(Name,SubjectName,Grade) values(‘jimmy‘,‘English‘,90)
insert into #Grade(Name,SubjectName,Grade) values(‘tom‘,‘Math‘,60)
insert into #Grade(Name,SubjectName,Grade) values(‘tom‘,‘Chinese‘,80)
insert into #Grade(Name,SubjectName,Grade) values(‘tom‘,‘English‘,90)
insert into #Grade(Name,SubjectName,Grade) values(‘lina‘,‘Math‘,110)
insert into #Grade(Name,SubjectName,Grade) values(‘lina‘,‘Chinese‘,120)
insert into #Grade(Name,SubjectName,Grade) values(‘lina‘,‘English‘,50)
--step 1
select Name,
       (case when SubjectName=‘Math‘ then Grade else 0 end) as Math,
       (case when SubjectName=‘Chinese‘ then Grade else 0 end) as Chinese,
       (case when SubjectName=‘English‘ then Grade else 0 end) as English,
       Grade
from #Grade 
--step 2
select Name,
       Max(case when SubjectName=‘Math‘ then Grade else 0 end) as Math,
       Max(case when SubjectName=‘Chinese‘ then Grade else 0 end) as Chinese,
       Max(case when SubjectName=‘English‘ then Grade else 0 end) as English,
       Sum(Grade)/3 as Average
from #Grade 
group by Name
order by Average desc
--drop temp table
drop table #Grade