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 got an overview of PIVOT with this.

Now, your turn....

Share with me how you are using PIVOT table in your project. In case of any questions, kindly leave your comment & I will try to get back to you.