Tuesday, 31 May 2016

SQL Query for generating matrix in SQL Server

We have a table like that:
























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