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

stackoverflow.com/questions/28804776/postgresql-9-3-pivot-table — Nick
– Nick, Commented Jun 13, 2016 at 18:20

Nick · Accepted Answer · 2016-06-13 19:42:45Z

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, 2016 at 19:42

answered Jun 13, 2016 at 18:40

Nick

7,5012 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Garrick Brim Over a year ago

Thank you, the logic here helped!

Garrick Brim Over a year ago

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.

Nick Over a year ago

@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.

GoneTaPlaid · Accepted Answer · 2016-06-13 18:41:14Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related