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 EmployeeExpenses) ee
FOR TravelMedium IN ([Train],[Bus])
) as pvt
Below is the result:
Hope so you get an overview of PIVOT with this.