SQL 在不使用 Oracle Pivot 子句的情况下创建数据透视表

Question

我正在尝试创建一个数据透视表，但没有使用oracle pivot子句。

当我运行此查询时，我得到以下数据：

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      ║
╚════════╩═════════════╩════════════╝

现在，我想生成一个以透视表布局显示相同数据的报表：

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

经过大量的实验和试验，我的代码变得越来越大，它返回了绝对总数：

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  ║  
╚════════╩══════════╩════════════╩════════╝

关于如何使其有效并仅返回每辆卡车的每位司机计数的任何想法？

谢谢

Answer 1

使用透视查询并仅聚合名字：

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

您可以使用条件聚合：

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;