Tuesday, December 25, 2012

SQL - CrossTab Queries using PIVOT

Today, we would be going through CrossTab queries using the PIVOT function available in SQL.
So let's start....and consider the below scenario

Below is the design of the table

Below is the data present in the table

Now, we want to get data in the manner so that we can see how much money Employee has spent on each travel medium. So, we would be making use of PIVOT here

select EmployeeName,[Train] as Train,[Bus] as Bus
select EmployeeName,TravelMedium,AmountSpent
from EmployeeExpenses)  ee
FOR TravelMedium IN ([Train],[Bus])
) as pvt

Below is the result:

Hope so you get an overview of PIVOT with this.

No comments:

Post a Comment