I know when we talk about the pivoting & unpivoting the data then most of the time we are making our 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 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 following table of employee with id,name,weekid and Dayname columns.
DECLARE @tblEmployeeDayWiseAttendace AS TABLE (Id INT IDENTITY(1,1),
Now let’s insert few rows into it
INSERT INTO @tblEmployeeDayWiseAttendace (EmployeeName,WeekId,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)
If you run SELECT * FROM @tblEmployeeDayWiseAttendace then you will get following data as shown in below snap.
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
Once you run this query you will get the output which you require.
Now, tell me do you still afraid from unpivot.
Share your thoughts & inputs in comment.