Garrick Brim Garrick Brim - 5 months ago 19
SQL Question

How can I combine multiple rows into one row? SQL PostgreSQL Admin

I have mapped two tables based on their common ID and but I would like to have all of the phone numbers associated with a particular company on one row instead of multiple rows, in three columns "Telephone", "Fax", and "Toll-Free". Below is an example of how the data output.



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

Probably the most simple way to do this is to use aggregation:

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;
Comments