关于sql行转列的探讨,由一道试题引出.docVIP

Sql综合查询与行转列示例 一道试题,查询结果为最后一个表. 如图： 示例代码一： create table EducationLevel(LevelId int primary key,LevelName varchar(50),Grades int) insert into EducationLevel values (1,博士,50) insert into EducationLevel values (2,硕士,40) insert into EducationLevel values (3,本科,30) insert into EducationLevel values (4,专科,20) insert into EducationLevel values (5,专科以下,10) create table Department(DepartmentId int primary key,DepartmentName varchar(50)) insert into Department values (1,历史系) insert into Department values (2,经济学院) insert into Department values (3,管理学院) insert into Department values (4,外语系) create table Personnel(PersonId int primary key,PersonName varchar(50), EducationLevel int,Department int) insert into Personnel values (1,刘雪飞,3,1) insert into Personnel values (2,张红霞,3,1) insert into Personnel values (3,王刚,1,2) insert into Personnel values (4,李良,4,2) insert into Personnel values (5,肖楠,1,4) insert into Personnel values (6,于涛,3,4) insert into Personnel values (7,孙小雪,4,3) insert into Personnel values (8,高溪,3,3) insert into Personnel values (9,赵柯,4,3) insert into Personnel values (10,刘鹏飞,2,4) select department,departmentname as 院系, sum(case when levelname=博士 then 1 else 0 end) as 博士人数, max(case when levelname=博士 then grades else 0 end) as 博士分数, sum(case when levelname=硕士 then 1 else 0 end) as 硕士人数, max(case when levelname=硕士 then grades else 0 end) as 硕士分数, sum(case when levelname=本科 then 1 else 0 end) as 本科人数, max(case when levelname=本科 then grades else 0 end) as 本科分数, sum(case when levelname=专科 then 1 else 0 end) as 专科人数, max(case when levelname=专科 then grades else 0 end) as 专科分数, sum(case when levelname=专科以下 then 1 else 0 end) as 专科以下人数, max(case when levelname=专科以下 then grades else 0 end) as 专科以下分数, sum(grades) as 汇总 from (select a.*,b.levelname,b.grades,c.departmentname from personnel a inner join educationlevel b on a.educationlevel=b.levelid inner join department c on a.department=c.departmentid) t group by department,departmentname order by department --或者 sql语句 select a