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

Sql综合查询与行转列示例一道试题,查询结果为最后一个表.如图：示例代码一：createtableEducationLevel(LevelIdintprimarykey,LevelNamevarchar(50),Gradesint)insertintoEducationLevelvalues(1,'博士',50)insertintoEducationLevelvalues(2,'硕士',40)insertintoEducationLevelvalues(3,'本科',30)insertintoEducationLevelvalues(4,'专科',20)insertintoEducationLevelvalues(5,'专科以下',10)createtableDepartment(DepartmentIdintprimarykey,DepartmentNamevarchar(50))insertintoDepartmentvalues(1,'历史系')insertintoDepartmentvalues(2,'经济学院')insertintoDepartmentvalues(3,'管理学院')insertintoDepartmentvalues(4,'外语系')createtablePersonnel(PersonIdintprimarykey,PersonNamevarchar(50),EducationLevelint,Departmentint)insertintoPersonnelvalues(1,'刘雪飞',3,1)insertintoPersonnelvalues(2,'张红霞',3,1)insertintoPersonnelvalues(3,'王刚',1,2)insertintoPersonnelvalues(4,'李良',4,2)insertintoPersonnelvalues(5,'肖楠',1,4)insertintoPersonnelvalues(6,'于涛',3,4)insertintoPersonnelvalues(7,'孙小雪',4,3)insertintoPersonnelvalues(8,'高溪',3,3)insertintoPersonnelvalues(9,'赵柯',4,3)insertintoPersonnelvalues(10,'刘鹏飞',2,4)selectdepartment,departmentnameas'院系',sum(casewhenlevelname='博士'then1else0end)as'博士人数',max(casewhenlevelname='博士'thengradeselse0end)as'博士分数',sum(casewhenlevelname='硕士'then1else0end)as'硕士人数',max(casewhenlevelname='硕士'thengradeselse0end)as'硕士分数',sum(casewhenlevelname='本科'then1else0end)as'本科人数',max(casewhenlevelname='本科'thengradeselse0end)as'本科分数',sum(casewhenlevelname='专科'then1else0end)as'专科人数',max(casewhenlevelname='专科'thengradeselse0end)as'专科分数',sum(casewhenlevelname='专科以下'then1else0end)as'专科以下人数',max(casewhenlevelname='专科以下'thengradeselse0end)as'专科以下分数',sum(grades)as'汇总'from(selecta.*,b.levelname,b.grades,c.departmentnamefrompersonnelainnerjoineducationlevelbona.educationlevel=b.levelidinnerjoindepartmentcona.department=c.departmentid)tgroupbydepartment,departmentnameorderbydepartment--或者sql语句selecta.department,c.departmentnameas'院系',sum(casewhenb.levelname='博士'then1else0end)as'博士人数',max(casewhenb.levelname='博士'thenb.gradeselse0end)as'博士分数',sum(casewhenb.levelname='硕士'then1else0end)as'硕士人数',max(casewhenb.levelname='硕士'thenb.gradeselse0end)as'硕士分数',sum(casewhenb.levelname='本科'then1else0end)as'本科人数',max(casewhenb.levelname='本科'thenb.gradeselse0end)as'本科分数',sum(casewhenb.levelname='专科'then1else0end)as'专科人数',max(casewhenb.levelname='专科'thenb.gradeselse0end)as'专科分数',sum(casewhenb.levelname='专科以下'then1else0end)as'专科以下人数',max(casewhenb.levelname='专科以下'thenb.gradeselse0end)as'专科以下分数',sum(b.grades)as'汇总'frompersonnela,educationlevelb,departmentcwherea.educationlevel=b.levelidanda.department=c.departmentidgroupbya.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+casedeclare@sqlasnvarchar(max);set@sql='';select@sql=@sql+',sum(casee.LevelIDwhen'+cast(LevelIDasvarchar)+'then1else0end)as'+LevelName+',sum(casee.LevelIDwhen'+cast(LevelIDasvarchar)+'thene.Gradeselse0end)as'+LevelName+'分数'fromEducationLevelorderbyLevelID;set@sql='selectd.DepartmentName'+@sql+',sum(e.Grades)as总分数fromPersonnelpinnerjoinDepartmentdonp.Department=d.DepartmentIDinnerjoinEducationLeveleonp.EducationLevel=e.LevelIDgroupbyd.DepartmentName,d.DepartmentIDorderbyd.DepartmentID';--select@sql;execsp_executesql@sql;利用pivot(sqlserver2005版本及以上)，用于行转列。兼容级别设置为90及以上才行：--查询某个数据库的兼容级别cmptlevel：usemasterselectcmptlevelas'兼容级别',*fromsysdatabaseswherename='Northwind'--更改兼容级别execsp_dbcmptlevelmyDatabaseName,90语法：PIVOT(聚合函数(要聚合的列)FOR[包含要成为列标题的值的列]IN([第一个透视的列],[第二个透视的列],...[最后一个透视的列]))AS透视表的别名pivot与以前的方式一样，只是变了一下写法，所以也要用聚合，就像groupby里面没有的字段也要用聚合每个pivot只能聚合一列--分数select*from(selecta.*,b.levelname,b.grades,c.departmentnamefrompersonnela,educationlevelb,departmentcwherea.educationlevel=b.levelidanda.department=c.departmentid)tpivot(max(grades)forlevelnamein([博士分数],[硕士分数],[本科分数],[专科分数],[专科以下分数]))astGrade--人数select*from(selecta.*,b.levelname,b.grades,c.departmentnamefrompersonnela,educationlevelb,departmentcwherea.educationlevel=b.levelidanda.department=c.departmentid)tpivot(count(PersonId)forlevelnamein([博士人数],[硕士人数],[本科人数],[专科人数],[专科以下人数]))astCount--然后将两表tGrade、tCount连接查询(ontGrade.departmentname=t