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