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

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 ainner join educationlevel b on cationlevel=b.levelidinner join department c on a.department=c.departmentid) tgroup by department,departmentnameorder by department-或者 sql语句select a.department,c.departmentname as 院系,sum(case when b.levelname=博士 then 1 else 0 end) as 博士人数,max(case when b.levelname=博士 then b.grades else 0 end) as 博士分数,sum(case when b.levelname=硕士 then 1 else 0 end) as 硕士人数,max(case when b.levelname=硕士 then b.grades else 0 end) as 硕士分数,sum(case when b.levelname=本科 then 1 else 0 end) as 本科人数,max(case when b.levelname=本科 then b.grades else 0 end) as 本科分数,sum(case when b.levelname=专科 then 1 else 0 end) as 专科人数,max(case when b.levelname=专科 then b.grades else 0 end) as 专科分数,sum(case when b.levelname=专科以下 then 1 else 0 end) as 专科以下人数,max(case when b.levelname=专科以下 then b.grades else 0 end) as 专科以下分数,sum(b.grades) as 汇总from personnel a,educationlevel b,department c where cationlevel=b.levelid and a.department=c.departmentidgroup by a.department,c.departmentname示例代码二(一位网络大神的写法)：-以下代码适用于sqlserver2005及以上版本-测试数据-教育水平表ifobject_id(EducationLevel,U)isnotnulldroptableEducationLevel;GOcreatetableEducationLevel(LevelIDint,LevelNamenvarchar(10),Gradesint);insertintoEducationLevelselect1,博士,50unionallselect2,硕士,40unionallselect3,本科,30unionallselect4,专科,20unionallselect5,专科以下,10;-院系表ifobject_id(Department,U)isnotnulldroptableDepartment;GOcreatetableDepartment(DepartmentIDint,DepartmentNamenvarchar(100)insertintoDepartmentselect1,历史系unionallselect2,经济学院unionallselect3,管理学院unionallselect4,外语系;-人员表ifobject_id(Personnel,U)isnotnulldroptablePersonnel;GOcreatetablePersonnel(PersonIDint,PersonNamenvarchar(20),EducationLevelint,Departmentint)insertintoPersonnelselect1,刘雪飞,3,1unionallselect2,张红霞,3,1unionallselect3,王钢,1,2unionallselect4,李良,4,2unionallselect5,肖楠,1,4unionallselect6,于涛,3,4unionallselect7,孙小雪,4,3unionallselect8,高溪,3,3unionallselect9,赵柯,4,3unionallselect10,刘鹏飞,2,4;-动态sql构造聚合语句，生成交叉报表-因为有两个聚合项，所以不使用pivot，而是使用sum+casedeclaresqlasnvarchar(max);setsql=;selectsql=sql+,sum(casee.LevelIDwhen+cast(LevelIDasvarchar)+then1else0end)as+LevelName+,sum(casee.LevelIDwhen+cast(LevelIDasvarchar)+thene.Gradeselse0end)as+LevelName+分数fromEducationLevelorderbyLevelID;setsql=selectd.DepartmentName+sql+,sum(e.Grades)as总分数fromPersonnelpinnerjoinDepartmentdonp.Department=d.DepartmentIDinnerjoinEducationLeveleonp.EducationLevel=e.LevelIDgroupbyd.DepartmentName,d.DepartmentIDorderbyd.DepartmentID;-selectsql;execsp_executesqlsql;利用pivot(sql server 2005版本及以上)，用于行转列。兼容级别设置为90及以上才行：-查询某个数据库的兼容级别cmptlevel：use masterselect cmptlevel as 兼容级别,* from sysdatabases where name=Northwind-更改兼容级别exec sp_dbcmptlevel myDatabaseName,90语法：PIVOT()FORIN(第一个透视的列,第二个透视的列,.最后一个透视的列)ASpivot与以前的方式一样，只是变了一下写法，所以也要用聚合，就像groupby里面没有的字段也要用聚合 每个pivot只能聚合一列-分数select * from (select a.*,b.levelname,b.grades,c.departmentname from personnel a,educationlevel b,department c where cationlevel=b.levelid and a.department=c.departmentid) t pivot (max(grades) for levelname in (博士分数,硕士分数,本科分数,专科分数,专科以下分数) as tGrade-人数select * from (select a.*,b.levelname,b.grades,c.departmentname from personnel