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?

You surely don't want two columns with the same name? More like Number1 and Number2? And you'll want deterministic results. Not having the next identical call switch numbers? So which number shall go first? What's the exact table definition of details (incl. the primary key)? — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 24, 2023 at 21:54

user330315 · Accepted Answer · 2023-01-24 21:30:52Z

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.

answered Jan 24, 2023 at 21:30

user330315

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

Comments

Hassaan Anwar · Accepted Answer · 2023-01-24 21:35:10Z

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.

Collectives™ on Stack Overflow

Transpose in Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related