一個簡單的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語句了。這個功能感覺還是不錯，不過合計好像用這種方法不太好搞。不知道各位同行有沒有什麼好辦法。
