Pivot email rows into multiple columns per id

All we need is an easy explanation of the problem, so here it is.

I have a table "emails":

I need this result:

the number of columns could expand in future. In fact, in the real database, I have ids with 10 emails already.

Every id, email pair is unique in the table.

I will need to join the resulant table with another table "users" on id.

I’m using Postgres 13.3

This answer is close to my needs, I just need it to be in different columns: https://stackoverflow.com/a/15847245

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can aggregate the emails into an array:

select id, array_agg(email) as all_emails
from the_table
group by id;

This can be used to join against the users table. To put the emails into columns, extract them from the array.

select u.*, 
       e.all_emails[1] as email1, 
       e.all_emails[2] as email2, 
       e.all_emails[3] as email3, 
       e.all_emails[4] as email4 
from users u
  join (
    select id, array_agg(email) as all_emails
    from the_table
    group by id
  ) e on e.id = u.id;

If you need more emails, add more expressions to the outer query. If an index position does not exist, null is returned instead (no error).

It is not possible to write query that dynamically returns a different number of columns each time you call it. The number, type and name of all columns of a query must be known before the query starts running.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply