All we need is an easy explanation of the problem, so here it is.
I have a table "emails":
I need this result:
|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
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.
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 as email1, e.all_emails as email2, e.all_emails as email3, e.all_emails 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 🙂