SQL Server – Building CrossTab Queries – PART 3

SQL Server – Building CrossTab Queries – PART 3

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;

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

Transact-SQL

1

2

3

IFOBJECT_ID('PivotTable')ISNOTNULL

DROPTABLEPivotTable

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:

Transact-SQL

1

2

3

4

5

6

7

8

9

SELECTLName,High,Low,Medium

INTOPivotTable

FROM(SELECTLName,High,Low,Medium

FROM(SELECTLName,Class,SalaryFromStudents)sq

PIVOT

(SUM(Salary)

FORClassIN(High,Low,Medium)

)ASpt

)ASQ

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

Transact-SQL

1

SELECT*FROMPivotTable

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

Transact-SQL

1

2

3

4

5

SELECTLName,Class,SalaryFROMPivotTable

UNPIVOT

(Salary

FORClassIN(High,Low,Medium)

)ASsq

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.

Share This Story, Choose Your Platform!

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.