一个简单的SQL 行列转换语句

MsSQL 2019-01-04

一个简单的sql 行列转换
Author: eaglet
在数据库开发中经常会遇到行列转换的问题，比如下面的问题，部门，员工和员工类型三张表，我们要统计类似这样的列表
部门编号部门名称合计正式员工临时员工辞退员工
1 A 30 20 10 1
这种问题咋一看摸不着头绪，不过把思路理顺后再看，本质就是一个行列转换的问题。下面我结合这个简单的例子来实现行列转换。
下面3张表
<div class="codetitle"><a style="CURSOR: pointer" data="47172" class="copybut" id="copybut47172" onclick="doCopy('code47172')"> 代码如下:

<div class="codebody" id="code47172">
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 )
)

描述部门，员工和员工类型之间的关系。
插入测试数据
<div class="codetitle"><a style="CURSOR: pointer" data="38886" class="copybut" id="copybut38886" onclick="doCopy('code38886')"> 代码如下:

<div class="codebody" id="code38886">
insert Department values ( 1,' A ' );
insert Department values ( 2,' B ' );
insert Employee values ( 1,1,' Bob ' );
insert Employee values ( 2,' John ' );
insert Employee values ( 3,' May ' );
insert Employee values ( 4,2,' Tom ' );
insert Employee values ( 5,' Mark ' );
insert Employee values ( 6,' 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 正式
现在我们需要输出这样一个列表
部门编号部门名称合计正式员工临时员工辞退员工
这个问题我的思路是首先统计每个部门的员工类型总数
这个比较简单，我把它做成一个视图
<div class="codetitle"><a style="CURSOR: pointer" data="16859" class="copybut" id="copybut16859" onclick="doCopy('code16859')"> 代码如下:

<div class="codebody" id="code16859">
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,EmployeeType.EmployeeType
GO

现在 select * from VDepartmentEmployeeType
Id Department EmployeeType Cnt
----------- ---------- ------------ -----------
2 B 辞退 1
1 A 临时 1
1 A 正式 2
2 B 正式 2
有了这个结果，我们再通过行列转换，就可以实现要求的输出了
行列转换采用 case 分支语句来实现，如下：
<div class="codetitle"><a style="CURSOR: pointer" data="56380" class="copybut" id="copybut56380" onclick="doCopy('code56380')"> 代码如下:<div class="codebody" id="code56380">
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的方式来解决这个问题。看下面代码
<div class="codetitle"><a style="CURSOR: pointer" data="37818" class="copybut" id="copybut37818" onclick="doCopy('code37818')"> 代码如下:<div class="codebody" id="code37818">
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
<div class="codetitle"><a style="CURSOR: pointer" data="20064" class="copybut" id="copybut20064" onclick="doCopy('code20064')"> 代码如下:<div class="codebody" id="code20064">
SELECT Id 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语句了。这个功能感觉还是不错，不过合计好像用这种方法不太好搞。不知道各位同行有没有什么好办法。

行列转换

SQL Server2012高可用之日志传送测试

（一）日志传送架构（1.1）相关服务器主服务器：用于生产的服务器，上面运行这生产SQL Server数据库...

SQL server事物复制报错：要复制的 LOB 数据的长度(xxxxx)超出了配置的最大值 65536

(一)事故背景最近在SQL Server 2012生产数据库上配置完事物复制（发布订阅）后，生产数据库业务出现了...

SQL Server2012高可用之事物复制(发布订阅)测试

（一）测试目的目前公司使用的SQL SERVER 2012高可用环境为主备模式，其中主库可执行读写操作，备库既...

使用zabbix监控sql server的发布订阅

（一）背景个人在使用sql server时，用到了sql server的发布订阅来做主从同步，类似MySQL的异步复制。在...

SQL学习（四）集合运算

UNION和OR谓词找出 product 和 product2 中售价高于 500 的商品的基本信息. select * from product wh...

SQL学习(三）复杂查询

datawhale组队学习task03

一个简单的SQL 行列转换语句

相关文章