PIVOT in SQL Server 2005
We can use PIVOT relational operator to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. Source msdn.
Here is an example I was working on.
I have a table called Student and with 3 columns Subject, Grade and Student_Id. I am looking for output which is aggregated along the Subject. Something shown below.
Subject A B C D
-----------------------------------
MATH 20 10 30 50
PHY 20 10 50 30
CHEM 30 20 50 10
BIO 30 10 20 50
Its not easy to aggregate along the subject using the group by statement and the case statement. (Atleast I dont know of any easy way!!!) But using the Pivot operator makes this very simple.
Running the above query displays the information as shown below.
UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows.