Pivot email rows into multiple columns per id

Question

I have a table "emails":

id	email
1	[email protected]
2	[email protected]
2	[email protected]
4	[email protected]
2	[email protected]
4	[email protected]

I need this result:

id	email	email	email
1	[email protected]
2	[email protected]	[email protected]	[email protected]
4	[email protected]	[email protected]

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

Answer 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.