浅谈SQL Server 之 PIVOT运算符用法

相信大家在处理数据库编程时，也许会常用到pivot运算符。今天把我近段时间处理的一个简单报表用到pivot运算符与大家分享一下。

比如,针对一个职员基础表tb_Employee(ID,EmpID,EmpName,DptNo,DptDesc,InDate,...),利用pivot分析职工流动率情况。在入职日期不确定的情况下，如何动态处理所要的结果为题。

即，如何实现：

SELECT DptNo,DptDesc,[2000] AS Y2000,[2001] AS Y2001,[2002] AS Y2002,...,[2017] AS Y2017

FROM (

　　 SELECT DptNo,DptDesc,YEAR(InDate) AS ExpYear

　　 FROM  tb_Employee WITH (NOLOCK)

　　 　) t 　　PIVOT (　　COUNT(ExpYear)　　FOR ExpYear  IN([2000],[2001],[2002],...,[2017])　　) AS　　dptDetail 

ORDER BY dptDetail

 

执行结果显示如图1所示：

 　　　　　　　　　　　　　　　　　　　　图 1

 

考虑到不同的部门入职(或离职)的年份不尽相同，要实现根据部门编号灵活呈现流动人员情况，比如编号“A01”的部门或许是2005年和2008年有入职(或离职)职员，而编号是“A02”的部门则是2002年、2005年、2010年及2012年有流动人员信息，等等。为灵活处理此类信息，我个人编写存储过程以动态实现需求，部分编码分享如下，请各位加以指点：

（一、）定义存储过程及需传入的参数

--

--　　@ dptno varchar(20)

--1.声明变量

DECLARE @sql　　VARCHAR(2000),@sql2　　VARCHAR(2000),@dptno VARCHAR(20)

SET @sql='SELECT DptNo'

SET @sql2='COUNT(ExpYear)　　FOR　　ExpYear　　IN([1900]'　　 --[1900]为虚设

SET @dptno='A01'

--2. 利用游标确定该职工表的所有年份

--2.1.　　定义游标

DECLARE cur_ExpYear　　CURSOR 

FOR　　

SELECT ROW_NUMBER()OVER(ORDER BY t1.ExpYear) AS sn,t1.ExpYear

FROM　　(SELECT DISTINCT　　YEAR(InDate) AS ExpYear　　FROM　　tb_Employee) t1

--2.2.　　打开游标

OPEN cur_ExpYear

--2.3.　　使用游标

DECLARE @sn TINYINT,@expyear VARCHAR(10)　　FETCH NEXT FROM　　cur_ExpYear　　INTO　　@sn,@expyear

WHILE @@FETCH_STATUS=0

BEGIN

　　SET @sql=RTRIM(@sql)+',['+@expyear+']'+'　　AS　　Y'+@expyear

　　SET @sql2=RTRIM(@sql2)+',['+@expyear+']'

　　NEXT FROM cur_ExpYear　　INTO 　　@sn,@expyear

END　

--2.4.　　关闭游标并释放游标

CLOSE　　 cur_ExpYear

DEALLOCATE　　cur_ExpYear　

--3.实现目标

SET @sql=RTRIM(@sql)　　+'　　FROM (SELECT DptNo,YEAR(InDate) AS ExpYear　　FROM　　tb_Employee WITH (NOLOCK)　)  t PIVOT

('　　+@sql2+')'　　+') AS dptDetail　　ORDER BY DptNo　　'

PRINT (@sql)

EXEC (@sql)　

 

 

在输入部门编号为"A01"时，执行则显示如图2所示：

　　图 2

当输入部门编号为"B02"时，执行显示结果如图3所示：

　　图 3

至此，不管职工表如何变化都能实现需求。