We have a table like that:
We need result of query in MATRIX form like this:
We can use SQL Server's PIVOT operator
We need result of query in MATRIX form like this:
We can use SQL Server's PIVOT operator
;WITH Q AS (
SELECT [Vehicle] = 'Scooter' , [Employee]
= 'Anas', [Amount] = 80115.50
UNION
ALL SELECT 'Scooter', 'Ross', 36571.85
UNION
ALL SELECT 'Scooter', 'Michelle', 39571.97
UNION
ALL SELECT 'Car', 'Peterson', 82658.23
UNION
ALL SELECT 'Car', 'Ross', 68998.85
UNION
ALL SELECT 'Car', 'Anas', 63598.75
UNION
ALL SELECT 'Car', 'Smith', 58950.53
UNION
ALL SELECT 'Car', 'Andrew', 57890.21
UNION
ALL SELECT 'Van', 'Andrew', 82658.23
UNION
ALL SELECT 'Van', 'Smith', 13400.65
UNION
ALL SELECT 'Van', 'Deniss', 15430.10
UNION
ALL SELECT 'Bus', 'Anas', 95867.55
UNION
ALL SELECT 'Bus', 'Bob', 98222.85
UNION
ALL SELECT 'Bus', 'Ronald', 98547.52
UNION
ALL SELECT 'Bus', 'Eric', 57880.65
UNION
ALL SELECT 'Bus', 'Smith', 6430.35
UNION
ALL SELECT 'Truck', 'Ross', 29560.10
UNION
ALL SELECT 'Truck', 'Andrew', 25780.30
UNION
ALL SELECT 'Truck', 'Taniya', 25054.44
)
SELECT Employee,
Scooter = ISNULL( Scooter, 0 ),
Car = ISNULL( Car, 0 ),
Van = ISNULL( Van, 0 ),
Bus = ISNULL( Bus, 0 ),
Truck = ISNULL( Truck, 0 ),
Total = ISNULL( Scooter, 0 )+ ISNULL( Car, 0 ) + ISNULL( Van, 0 )+ ISNULL( Bus, 0 ) + ISNULL( Truck, 0 )
FROM (
SELECT * FROM Q
)
AB
PIVOT ( SUM(Amount ) FOR [Vehicle] IN ([Scooter],[Car], [Van], [Bus], [Truck] )) PVT_table
No comments:
Post a Comment