PIVOT and UNPIVOT Operations in sql server

Introduction :

PIVOT turns unique values from one column in a table to multiple columns and also performs aggregation on other columns if required . UNPIVOT performs the reverse operation of PIVOT .
PIVOT does not actually change the structure of table in database , it only turns the data to view it from different perspectives.

Example using PIVOT and UNPIVOT :

Here I will take a simple example to illustrate PIVOT and UNPIVOT operations.
Let us suppose we have a table with the following information employee id  ,day in a week and working hours of employee. (Just to illustrate ,I have considered data of 5 days in a week , scenarios could be different )

Now if we want to generate a report of an employee with his average or sum of working hours for all the days in a week  i.e if you want to turn the row information into columns as below , we can use PIVOT and to revert it use UNPIVOT.

Code – PIVOT Operation

BEGIN     DECLARE @Emp_Attendance AS TABLE     (         EmpId INT ,         [DAY] VARCHAR(10),         WorkingHrs INT     )     INSERT INTO @Emp_Attendance VALUES(1,'MON',8)     INSERT INTO @Emp_Attendance VALUES(1,'TUE',7)     INSERT INTO @Emp_Attendance VALUES(1,'WED',4)     INSERT INTO @Emp_Attendance VALUES(1,'THU',5)     INSERT INTO @Emp_Attendance VALUES(1,'FRI',6)     INSERT INTO @Emp_Attendance VALUES(2,'MON',6)     INSERT INTO @Emp_Attendance VALUES(2,'TUE',9)     INSERT INTO @Emp_Attendance VALUES(2,'WED',5)     INSERT INTO @Emp_Attendance VALUES(2,'THU',8)     INSERT INTO @Emp_Attendance VALUES(2,'FRI',4)     /*PIVOT*/     SELECT EmpId ,[Mon] ,[Tue],[Wed],[Thu],[Fri] FROM     (SELECT EmpId ,DAY,WorkingHrs FROM @Emp_Attendance) AS A     PIVOT     (     AVG(WorkingHrs)     FOR DAY IN ([Mon] ,[Tue],[Wed],[Thu],[Fri])     )AS B END GO

Code – UNPIVOT Operation

BEGIN     DECLARE @Emp_Attendance AS TABLE     (         EmpId INT ,         [DAY] VARCHAR(10),         WorkingHrs INT     )     INSERT INTO @Emp_Attendance VALUES(1,'MON',8)     INSERT INTO @Emp_Attendance VALUES(1,'TUE',7)     INSERT INTO @Emp_Attendance VALUES(1,'WED',4)     INSERT INTO @Emp_Attendance VALUES(1,'THU',5)     INSERT INTO @Emp_Attendance VALUES(1,'FRI',6)     INSERT INTO @Emp_Attendance VALUES(2,'MON',6)     INSERT INTO @Emp_Attendance VALUES(2,'TUE',9)     INSERT INTO @Emp_Attendance VALUES(2,'WED',5)     INSERT INTO @Emp_Attendance VALUES(2,'THU',8)     INSERT INTO @Emp_Attendance VALUES(2,'FRI',4)     /*PIVOT*/     SELECT EmpId ,[Mon] ,[Tue],[Wed],[Thu],[Fri] INTO #temp_Pivot FROM     (SELECT EmpId ,DAY,WorkingHrs FROM @Emp_Attendance) AS A     PIVOT     (     AVG(WorkingHrs)     FOR DAY IN ([Mon] ,[Tue],[Wed],[Thu],[Fri])     )AS B     SELECT 'AFTER PIVOT'     SELECT * FROM #temp_Pivot     SELECT 'AFTER UNPIVOT'     /*UNPIVOT*/     SELECT EmpId , DAY,WorkingHrs FROM     (SELECT EmpId,Mon,Tue,Wed,Thu,Fri FROM #temp_Pivot ) AS A     UNPIVOT     (         WorkingHrs FOR DAY IN (Mon,Tue,Wed,Thu,Fri)     ) AS B END GO

Output :