How Easily You Can Unpivot the Pivot Data in SQL Server?

I know when we talk about pivoting & unpivoting the data, then most of the time, we are making faces and we feel it would be a tough task. Trust me, after reading this post, you feel unpivot is super easy.

Before jumping directly into unpivot, you might want to share pivot link to take a glimpse if you are not aware of it.

Pivot in SQL Server pivot.

Now, let us assume that we have the following table of employee with id, name, weekid and Dayname columns.

DECLARE @tblEmployeeDayWiseAttendace AS TABLE (Id INT IDENTITY(1,1),
EmployeeName VARCHAR(100),
WeekId SMALLINT,
Monday TINYINT,
Tuesday TINYINT,
Wednesday TINYINT,
Thursday TINYINT,
Friday TINYINT,
Saturday TINYINT,
Sunday TINYINT)

Now let’s insert few rows into it:

INSERT INTO @tblEmployeeDayWiseAttendace _
(EmployeeName,WeekId,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)
VALUES(‘Sandeep’,1,8,8,8,8,8,0,0),
(‘Sunil’,1,8,8,8,8,8,0,0),
(‘Shreya’,1,7,6,8,8,8,0,0),
(‘Shweta’,1,8,8,8,0,5,0,0),
(‘Priya’,1,8,8,8,8,8,8,0),
(‘Rashmi’,1,9,8,9,8,8,4,0),
(‘Bhushan’,1,4,8,5,8,2,0,0)

If you run SELECT * FROM @tblEmployeeDayWiseAttendace, then you will get the following data as shown in the below image:

Now, the challenge is to Convert Columns Monday, Tuesday, Wednesday and other day columns to row corresponding to employee and show their value.

To make it very easy, you have to write below CROSS APPLY query:

SELECT tmp.Id, tmp.EmployeeName,tmp.WeekId,tmp2.weekdayname,tmp2.weekValue
FROM @tblEmployeeDayWiseAttendace tmp 
CROSS APPLY(values(‘Monday’,tmp.Monday),
(‘Tuesday’,tmp.Tuesday),
(‘Wednesday’,tmp.Wednesday),
(‘Thursday’,tmp.Thursday),
(‘Friday’,tmp.Friday),
(‘Saturday’,tmp.Saturday),
(‘Sunday’,tmp.Sunday))tmp2(WeekDayname,weekValue)

Once you run this query, you will get the output which you require.

Now, tell me, are you still afraid of unpivot.

Share your thoughts & inputs in the comments below.

Cheers!