Transpose in Postgresql-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

Try using the following query:

SELECT 
    Name,
    MIN(CASE WHEN rn = 1 THEN Number END) AS Number1,
    MIN(CASE WHEN rn = 2 THEN Number END) AS Number2
FROM
    (SELECT 
        Name, Number,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Number) AS rn
    FROM name) t
GROUP BY  Name

This query will use the ROW_NUMBER() function to assign a unique row number to each phone number for each customer. The ROW_NUMBER() function is ordered by the Number column, so the lowest number will have a row number of 1, and the second lowest number will have a row number of 2, etc.

Then we use the outer query to group customer by name and use MIN() function to get the first and second number based on the row number.

This query will return the desired output, with two columns, one showing the customer's first phone number and the other showing their second phone number.

Note: The query above assumes that the phone number is unique for each customer. If a customer has duplicate phone numbers, the query will return the first one it encounters.