一个简单的SQL 行列转换

 

一个简单的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语句了。这个功能感觉还是不错，不过合计好像用这种方法不太好搞。不知道各位同行有没有什么好办法。