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