MS SQL 行列转换

--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

MS SQL 行列转换,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。