Transpose in Postgresql

Question

I am trying to design a database of customer details. Where customers can have up to two different phone numbers.

When I run the Select * command to bring out the customers that match criteria, I get this:

Name  |  Number
James |   12344532
James  |  23232422

I would like it to display all customers with two numbers this way:

Name  |   Number  | Number
James     12344532   23232422
John      32443322
Jude      12121212   23232422

I am using Postgresql server on Azure Data studio.

Please assist.

I tried using this command:

Select * FROM name.name,
min(details.number) AS number1,
max(details.number) AS number2
FROM name
JOIN details
ON name.id=details.id
GROUP BY name.name

I got this:

Name  |   Number  | Number

James     12344532   23232422

John      32443322   32443322

Jude      12121212   23232422

Customers with just 1 phone number gets duplicated in the table. How do I go about this?

Answer 1

I would aggregate the numbers into an array, then extract the array elements:

select n.name, 
       d.numbers[1] as number_1,
       d.numbers[2] as number_2
from name n
  join (
    select id, array_agg(number) as numbers
    from details
    group by id
  ) d on d.id = n.id
order by name;

This is also easy to extend if you have more than two numbers.