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