浅谈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
至此,不管职工表如何变化都能实现需求。