一个简单的SQL 行列转换 Author: eaglet 在数据库开发中经常会遇到行列转换的问题，比如下面的问题，部门，员工和员工类型三张表，我们要统计类似这样的列表 部门编号 部门名称 合计
一个简单的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 ) ) 描述部门，员工和员工类型之间的关系。 插入测试数据 复制代码 代码如下: insert Department values ( 1 , ' A ' ); insert Department values ( 2 , ' B ' ); insert Employee values ( 1 , 1 , ' Bob ' ); insert Employee values ( 2 , 1 , ' John ' ); insert Employee values ( 3 , 1 , ' May ' ); insert Employee values ( 4 , 2 , ' Tom ' ); insert Employee values ( 5 , 2 , ' Mark ' ); insert Employee values ( 6 , 2 , ' Ken ' ); insert EmployeeType values ( 1 , ' 正式 ' ); insert EmployeeType values ( 2 , ' 临时 ' ); insert EmployeeType values ( 3 , ' 正式 ' ); insert EmployeeType values ( 4 , ' 正式 ' ); insert EmployeeType values ( 5 , ' 辞退 ' ); insert EmployeeType values ( 6 , ' 正式 ' ); 看一下部门、员工和员工类型的列表 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 ' ) 执行结果如下： 部门编号 部门名称 辞退 临时 正式 合计 ----------- ---------- ----------- ----------- ----------- ----------- 1 A 0 1 2 3 2 B 1 0 2 3 这个结果和前面硬编码的结果是一样的，但我们通过程序来获取了所有的员工类型，这样做的好处是如果我们新增了一个员工类型，比如“合同工”，我们不需要修改程序，就可以得到我们想要的输出。 如果你的数据库是SQLSERVER 2005 或以上，也可以采用SQLSERVER2005 通过的新功能 PIVOT 复制代码 代码如下: SELECT Id as ' 部门编号 ' , Department as ' 部门名称 ' , [ 正式 ] , [ 临时 ] , [ 辞退 ] FROM ( SELECT Id,Department,EmployeeType,Cnt FROM VDepartmentEmployeeType) p PIVOT ( SUM (Cnt) FOR EmployeeType IN ( [ 正式 ] , [ 临时 ] , [ 辞退 ] ) ) AS unpvt 结果如下 部门编号 部门名称 正式 临时 辞退 ----------- ---------- ----------- ----------- ----------- 1 A 2 1 NULL 2 B 2 NULL 1 NULL 可以通过 ISNULL 函数来强制转换为0，这里我就不写出具体的SQL语句了。这个功能感觉还是不错，不过合计好像用这种方法不太好搞。不知道各位同行有没有什么好办法。
