一个简单的SQL 行列转换 Author: eaglet 在数据库开发中经常会遇到行列转换的问题，比如下面的问题，部门，员工和员工类型三张表，我们要统计类似这样的列表 部门编号 部门名称 合计 正式员工 临时员工 辞退员工 1 A 30 20 10 1 这种问题咋一看摸不着头绪，不过把思路理顺后再看，本质就是一个行列转换的问题。下面我结合这个简单的例子来实现行列转换。 下面3张表 复制代码 代码如下: if exists （ select * from sysobjects where id = object_id （ " EmployeeType " ） and type = " u " ） drop table EmployeeType GO if exists （ select * from sysobjects where id = object_id （ " Employee " ） and type = " u " ） drop table Employee GO if exists （ select * from sysobjects where id = object_id （ " Department " ） and type = " u " ） drop table Department GO create table Department （ Id int primary key , Department varchar （ 10 ） ） create table Employee （ EmployeeId int primary key , DepartmentId int Foreign Key （DepartmentId） References Department（Id） , -- DepartmentId , EmployeeName varchar （ 10 ） ） create table EmployeeType （ EmployeeId int Foreign Key （EmployeeId） References Employee（EmployeeId） , -- EmployeeId , EmployeeType varchar （ 10 ） ）
看一下部门、员工和员工类型的列表 Department EmployeeName EmployeeType ---------- ------------ ------------ A Bob 正式 A John 临时 A May 正式 B Tom 正式 B Mark 辞退 B Ken 正式 现在我们需要输出这样一个列表 部门编号 部门名称 合计 正式员工 临时员工 辞退员工 这个问题我的思路是首先统计每个部门的员工类型总数 这个比较简单，我把它做成一个视图 复制代码 代码如下: if exists （ select * from sysobjects where id = object_id （ " VDepartmentEmployeeType " ） and type = " v " ） drop view VDepartmentEmployeeType GO create view VDepartmentEmployeeType as select Department.Id, Department.Department, EmployeeType.EmployeeType, count （EmployeeType.EmployeeType） Cnt from Department, Employee, EmployeeType where Department.Id = Employee.DepartmentId and Employee.EmployeeId = EmployeeType.EmployeeId group by Department.Id, Department.Department, EmployeeType.EmployeeType GO
现在 select * from VDepartmentEmployeeType Id Department EmployeeType Cnt ----------- ---------- ------------ ----------- 2 B 辞退 1 1 A 临时 1 1 A 正式 2 2 B 正式 2 有了这个结果，我们再通过行列转换，就可以实现要求的输出了 行列转换采用 case 分支语句来实现，如下： 复制代码 代码如下: select Id as " 部门编号 " , Department as " 部门名称 " , [ 正式 ] = Sum （ case when EmployeeType = " 正式 " then Cnt else 0 end ）, [ 临时 ] = Sum （ case when EmployeeType = " 临时 " then Cnt else 0 end ）, [ 辞退 ] = Sum （ case when EmployeeType = " 辞退 " then Cnt else 0 end ）, [ 合计 ] = Sum （ case when EmployeeType <> "" then Cnt else 0 end ） from VDepartmentEmployeeType GROUP BY Id, Department
看一下结果 部门编号 部门名称 正式 临时 辞退 合计 ----------- ---------- ----------- ----------- ----------- ----------- 1 A 2 1 0 3 2 B 2 0 1 3 现在还有一个问题，如果员工类型不可以应编码怎么办？也就是说我们在写程序的时候并不知道有哪些员工类型。这确实是一个 比较棘手的问题，不过不是不能解决，我们可以通过拼接SQL的方式来解决这个问题。看下面代码 复制代码 代码如下: DECLARE @s VARCHAR （ max ） SELECT @s = isnull （ @s + " , " , "" ） + " [ " + ltrim （EmployeeType） + " ] = " + " Sum（case when EmployeeType = """ + EmployeeType + """ then Cnt else 0 end） " FROM （ SELECT DISTINCT EmployeeType FROM VDepartmentEmployeeType ） temp EXEC （ " select Id as 部门编号, Department as 部门名称, " + @s + " ,[合计]= Sum（case when EmployeeType <> """" then Cnt else 0 end） " + " from VDepartmentEmployeeType GROUP BY Id, Department " ）
易网时代-编程资源站