一個簡略的SQL 行列轉換語句。本站提示廣大學習愛好者：（一個簡略的SQL 行列轉換語句）文章只能為提供參考，不一定能成為您想要的結果。以下是一個簡略的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語句了。這個功效感到照樣不錯，不外算計似乎用這類辦法不太好弄。不曉得列位同業有無甚麼好方法。
