Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

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

5.00/5 (1 vote)
23 Apr 2018CPOL 3.7K  
How to easily unpivot 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.

SQL
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:

SQL
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:

Pivot table indiandotnet

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:

SQL
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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)