Hello Folks,

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.

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;

1_SQL_Server_Building_CrossTab_Queries_PART3

Now, drop the table if it exists with the same name:

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:

So you can see the data with the help of the query:

2_SQL_Server_Building_CrossTab_Queries_PART3

Now, we will bring back the column attributes back to the row with the query:

So you can see the result set as:

3_SQL_Server_Building_CrossTab_Queries_PART3

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

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook