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

Question

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

http://stackoverflow.com/questions/28804776/postgresql-9-3-pivot-table — Nick, Jun 13 '16 at 18:20

Nick · Accepted Answer · 2016-06-13T19:42:45.563

8

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;

edited Jun 13 '16 at 19:42

answered Jun 13 '16 at 18:40

Nick

7,103
2
21
43

1

Thank you, the logic here helped! – Garrick Brim Jun 13 '16 at 19:02
Hi @Nicarus what does the MAX or MIN in front of the CASE statement achieve? That seem to be the missing piece for me on an earlier attempt. – Garrick Brim Jun 14 '16 at 14:26
@GarrickBrim It "collapses" it into a single record per `company_name`. If you were to remove them, you would see that you would have more than one record. – Nick Jun 14 '16 at 15:20

GoneTaPlaid · Answer 2 · 2016-06-13T18:41:14.180

You can accomplish getting all the columns in one row by joining, and aliasing when you use the same table three times:

    SELECT
      fleet.company_name,
      telephone.phone_number AS Telephone,
      toll_free.phone_number AS Toll_Free,
      fax.phone_number AS Fax
    FROM 
      fleetseek.fleet
    JOIN 
      fleet_phone
    ON 
      fleet.fleet_id = fleet_phone.fleet_id
    LEFT OUTER JOIN (SELECT 
            phone_number
          FROM 
            fleetseek.phone_number, 
            fleetseek.phone_type
          WHERE 
            phone_number.phone_type_id = phone_type.phone_type_id AND
            phone_type.name = 'Telephone') telephone
    ON  
      fleet_phone.phone_number_id = telephone.phone_number_id
    LEFT OUTER JOIN 
      (SELECT 
         phone_number
       FROM 
         fleetseek.phone_number, 
         fleetseek.phone_type
       WHERE 
         phone_number.phone_type_id = phone_type.phone_type_id AND
         phone_type.name = 'Toll Free') toll_free
    ON  
      fleet_phone.phone_number_id = toll_free.phone_number_id
    LEFT OUTER JOIN 
      (SELECT 
         phone_number
       FROM 
         fleetseek.phone_number, 
         fleetseek.phone_type
       WHERE 
         phone_number.phone_type_id = phone_type.phone_type_id AND
         phone_type.name = 'Fax') fax
    ON  
      fleet_phone.phone_number_id = fax.phone_number_id;

General design choice suggestion: put your phone types in the same table as your phone numbers if that information is so important. Having to add a join will impact performance (while compromising space). Generally, performance is more expensive than space.

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

2 Answers2