sql - SQL将一张表的多条记录合并成另一张表的多列

Question

我正在使用 SQL 并有一个目标表：

Event ID (key) |   Road    |     total count  |     motorcycles   |    cars    |   trucks   |   bus

我有一个类似表的记录：

[Event ID    |    mode of transport      |       count
1           |      bus                  |          3
1           |      cars                 |         20
1           |      trucks               |          2 
1           |      motorcycles          |          5
2           |      bus                  |          1
2           |      cars                 |          12 
2           |      motorcycles          |          1][1]

（Event ID和运输方式的组合是唯一的）

如何轻松地将第二个表中的数据合并到第一个表中：

Event ID (key) |   Road    |     total count    |     motorcycles   |    cars    |   trucks   |   bus
1              |     ...   |      ...           |        5          |     20     |    2       |    3
2              |     ...   |      ...           |        1          |     12     |            |    1  

我正在寻找一种可以将第二个表中的记录数据合并到一个 SQL 结构/语句中的方法。谢谢！

标签： sql

Answer 1

您可以使用条件聚合：

select eventid, road,
       sum(count) as total_count,
       sum(case when mode = 'motorcycles' then count end) as cnt_motorcycles,
       sum(case when mode = 'car' then count end) as cnt_car,
       sum(case when mode = 'bus' then count end) as cnt_bus,
       sum(case when mode = 'truck' then count end) as cnt_truck
from t
group by eventid, road;