学步园

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