CODEWITHSUNDEEP
sqlpostgresql
Garrick Brim
Garrick Brim

Reputation: 87

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

Upvotes: 1

Views: 3201

Answers (2)

Nick
Nick

Reputation: 7441

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;

Upvotes: 8

GoneTaPlaid
GoneTaPlaid

Reputation: 29

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.

Upvotes: 0

Related Questions