PIVOT in SQL Server 2005

Vishnu — Mon, 13 Apr 2009 00:26:00 GMT

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.

Vishnu Prasad blog : Pivot

PIVOT in SQL Server 2005