You might have seen in my previous article post “Building CrossTab Queries – PART 2”, which deals with functioning of the PIVOT method, Case Expression Method and Dynamic Crosstab Queries. If you want to see it, you can browse the link from here;
Well this article is being based upon UNPIVOT method.
- It can also be seen as inverse of a crosstab query, which is extremely useful for normalizing denormalized data.
- It does this by twisting the data back to a normalized list, i.e., clockwise 90 degree.
- The UNPIVOT can only normalized the data supplied to it, so if the pivoted data is an aggregate summary, that’s all will be normalized, and the details won’t appear.
- This example should make you understand about the UNPIVOT are:
First, see the base table that we are using in the query;
Now, drop the table if it exists with the same name:
IF OBJECT_ID('PivotTable') IS NOT NULL DROP TABLE PivotTable GO
What we will do is that, first we show how the PivotTable looks and then with the help of UNPIVOT method, we will bring the table back to the Normal form, i.e., first changing from rows data to different column attributes (PIVOT) and then again bringing back all the column attributes back to the data rows (UNPIVOT).
So, now using the PIVOT method:
SELECT LName,High,Low,Medium INTO PivotTable FROM ( SELECT LName,High,Low,Medium FROM (SELECT LName,Class,Salary From Students) sq PIVOT (SUM(Salary) FOR Class IN (High, Low, Medium) ) AS pt ) AS Q
So you can see the data with the help of the query:
SELECT * FROM PivotTable
Now, we will bring back the column attributes back to the row with the query:
SELECT LName,Class,Salary FROM PivotTable UNPIVOT (Salary FOR Class IN (High, Low, Medium) ) AS sq
So you can see the result set as:
With this I come to an end of my article sequel on “Building CrossTab Queries”.
Hope you understand all the stuff’s which I presented here, and should help you to implement this idea in your projects.
Hope you got it understood well 🙂
And also comments on this!!