SQL create a pivot table without using the Oracle Pivot clause

Question

I'm trying to create a pivot table, but without using the oracle pivot clause.

When I run this query I get the following data:

SELECT first_name  AS driver 
, v.name AS truck
, COUNT(haul_id) AS "Haul Count"
FROM employee e JOIN haul h
  ON e.employee_id = h.employee_id
JOIN vehicle v
  ON v.vehicle_id = h.vehicle_id
GROUP BY first_name, v.name
ORDER BY first_name, COUNT(haul_id) DESC;

╔════════╦═════════════╦════════════╗
║ Driver ║  Truck      ║ Haul Count ║
╠════════╬═════════════╬════════════╣
║  Judy  ║ Big Dog     ║     5      ║
║  Judy  ║ Classy Cat  ║     5      ║
║  Judy  ║ Mad Max     ║     4      ║
║  Matt  ║ Mad Max     ║     8      ║
║  Matt  ║ Classy Cat  ║     8      ║
║  Matt  ║ Big Dog     ║     5      ║
║  Troy  ║ Mad Max     ║     8      ║
║  Troy  ║ Big Dog     ║     4      ║
║  Troy  ║ Classy Cat  ║     3      ║
╚════════╩═════════════╩════════════╝

Now I want to produce a report that shows the same data but in a pivot layout:

╔════════╦══════════╦════════════╦════════╗
║ Driver ║  Big Dog ║ Classy Cat ║ Mad Max║
╠════════╬══════════╬════════════╬════════╣
║  Judy  ║    5     ║     5      ║    4   ║
║  Matt  ║    5     ║     8      ║    8   ║
║  Troy  ║    4     ║     3      ║    8   ║  
╚════════╩══════════╩════════════╩════════╝

After a lot of experimenting and trials, my code just got bigger and bigger and it's returning the absolute totals:

SELECT first_name
, (SELECT COUNT(h.vehicle_id) 
FROM employee e JOIN haul h
 ON e.employee_id = h.employee_id
  JOIN vehicle v
    ON v.vehicle_id = h.vehicle_id
 WHERE v.name = 'Big Dog') AS "Big Dog"
 FROM haul JOIN vehicle
  ON haul.vehicle_id = vehicle.vehicle_id
 WHERE vehicle.name = 'Classy Cat') AS "Classy Cat"
 , (SELECT COUNT(haul.vehicle_id) 
    FROM haul JOIN vehicle
      ON haul.vehicle_id = vehicle.vehicle_id
    WHERE vehicle.name = 'Mad Max') AS "Mad Max"
 FROM employee e JOIN haul h
  ON e.employee_id = h.employee_id
   JOIN vehicle v
    ON v.vehicle_id = h.vehicle_id
 GROUP BY first_name
 ORDER BY first_name ASC;


 ╔════════╦══════════╦════════════╦════════╗
 ║ Driver ║  Big Dog ║ Classy Cat ║ Mad Max║
 ╠════════╬══════════╬════════════╬════════╣
 ║  Judy  ║    14    ║     16     ║    20  ║
 ║  Matt  ║    14    ║     16     ║    20  ║
 ║  Troy  ║    14    ║     16     ║    20  ║  
 ╚════════╩══════════╩════════════╩════════╝

Any ideas on how to make this effective and return just the count per driver for each truck?

Thanks

Answer 1

Use a pivot query and aggregate only over the first names:

SELECT
    first_name AS driver,
    SUM(CASE WHEN v.name = 'Big Dog'    THEN 1 ELSE 0 END) AS "Big Dog",
    SUM(CASE WHEN v.name = 'Classy Cat' THEN 1 ELSE 0 END) AS "Classy Cat",
    SUM(CASE WHEN v.name = 'Mad Max'    THEN 1 ELSE 0 END) AS "Max"
FROM employee e JOIN haul h
    ON e.employee_id = h.employee_id
JOIN vehicle v
    ON v.vehicle_id = h.vehicle_id
GROUP BY first_name
ORDER BY first_name

Answer 2

You can use conditional aggregation:

SELECT first_name
        , COUNT(CASE 
                   WHEN vehicle.name = 'Classy Cat' THEN h.vehicle_id 
                END) AS 'Classy Cat' 
        , COUNT(CASE 
                   WHEN vehicle.name = 'Big Dog' THEN h.vehicle_id 
                END) AS 'Big Dog' 
        , COUNT(CASE 
                   WHEN vehicle.name = 'Mad Max' THEN h.vehicle_id 
                END) AS 'Mad Max' 
FROM employee e 
JOIN haul h ON e.employee_id = h.employee_id
JOIN vehicle v ON v.vehicle_id = h.vehicle_id
GROUP BY first_name
ORDER BY first_name ASC;