sql - 如何将多行合并为一行？ SQL PostgreSQL 管理员

Question

我已经根据它们的公共(public) ID 映射了两个表，但我希望将与特定公司关联的所有电话号码放在一行而不是多行中，在三列“电话”、“传真”和“免费电话”。以下是数据输出方式的示例。

Dr. Dimes   5553456879  Telephone
Dr. Dimes   5553455600  Toll Free
Dr. Dimes   5553450123  Fax

SELECT 
  fleet.company_name, 
  phone_number.phone_number, 
  phone_type.name
FROM 
  fleetseek.phone_number, 
  fleetseek.phone_type, 
  fleetseek.fleet, 
  fleetseek.fleet_phone
WHERE 
  phone_number.phone_type_id = phone_type.phone_type_id AND
  fleet.fleet_id = fleet_phone.fleet_id AND
  fleet_phone.phone_number_id = phone_number.phone_number_id

Answer 1

可能最简单的方法是使用聚合:

SELECT 
  fleet.company_name,
  MAX(CASE WHEN phone_type.name = 'Telephone' THEN phone_number.phone_number END) AS telephone,
  MAX(CASE WHEN phone_type.name = 'Toll Free' THEN phone_number.phone_number END) AS toll_free,
  MAX(CASE WHEN phone_type.name = 'Fax' THEN phone_number.phone_number END) AS fax
FROM 
  fleetseek.phone_number, 
  fleetseek.phone_type, 
  fleetseek.fleet, 
  fleetseek.fleet_phone
WHERE 
  phone_number.phone_type_id = phone_type.phone_type_id AND
  fleet.fleet_id = fleet_phone.fleet_id AND
  fleet_phone.phone_number_id = phone_number.phone_number_id
GROUP BY
  fleet.company_name;