On the use of the pivot operator in SQL Server

It is believed that when you work with database programming, you might often use the pivot operator. Today, I'm going to use the pivot operator to share a simple report that I've been working on for a while.

For example, for an employee base table Tb_employee (Id,empid,empname,dptno,dptdesc,indate,...), pivot is used to analyze the turnover rate of workers. In the case of uncertain entry date, how to dynamically process the desired result is the problem.

That is, how to implement:

SELECT dptno,dpt,[2000] as y2000,[2001] as y2001,[2002] as Y2002,..., [] 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],..., [])) as Dptdetail

ORDER by Dptdetail

Taking into account the different years of entry (or separation) of the various departments, to achieve a flexible representation of the mobile personnel according to the department number, such as the number "A01" department may be in 2005 and 2008 have the entry (or separation) staff, and the number is "A02" department is 2002, 2005, There are mobile personnel information in 2010 and 2012, and so on. In order to flexibly handle this kind of information, I personally write stored procedures to dynamically implement the requirements, some of the code share is as follows, please give your advice:

(i) Define stored procedures and parameters to be passed in

--

--@ Dptno varchar (20)

--1. Declaring variables

DECLARE @sql varchar (in), @sql2 varchar (in), @dptno varchar (20)

SET @sql = ' SELECT dptno '

SET @sql2 = ' COUNT (expyear) for Expyear in ([1900] '--[1900] is dummy

SET @dptno = ' A01 '

--2. Use cursors to determine all years of the worker's Watch

--2.1. Defining cursors

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 cursor

OPEN Cur_expyear

--2.3. Using cursors

DECLARE @sn TINYINT, @expyear VARCHAR FETCH NEXT from the cur_expyear into @sn, @expyear

While @ @FETCH_STATUS =0

BEGIN

SET @sql =rtrim (@sql) + ', [' [Email protected]+ '] ' + ' as Y ' [email protected]

SET @sql2 =rtrim (@sql2) + ', [' [Email protected]+ '] '

NEXT from Cur_expyear to @sn, @expyear

END

--2.4. Close the cursor and release the cursor

CLOSE Cur_expyear

Deallocate cur_expyear

--3. Achieving goals

SET @sql =rtrim (@sql) + ' from (SELECT dptno,year (indate) as Expyear from Tb_employee with (NOLOCK)) T PIVOT

(' [email protected]+ ') ' + ') as Dptdetail ORDER by Dptno '

PRINT (@sql)

EXEC (@sql)

At this point, no matter how changes in the staff table can achieve demand.

Talking about the use of the pivot operator for SQL Server