Click here to Skip to main content
13,630,333 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

1.5K views
7 bookmarked
Posted 22 Apr 2018
Licenced CPOL

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

, 22 Apr 2018
Rate this:
Please Sign up or sign in to vote.
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.

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:

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:

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)

Share

About the Author

Rajat-Indiandotnet
Technical Lead
India India
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.180712.1 | Last Updated 23 Apr 2018
Article Copyright 2018 by Rajat-Indiandotnet
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid