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
from
(
select EmployeeName,TravelMedium,AmountSpent
from EmployeeExpenses)  ee
PIVOT
(
SUM(AmountSpent)
FOR TravelMedium IN ([Train],[Bus])
) as pvt





Below is the result:

Hope so you get an overview of PIVOT with this.