I have the following table, which gives multiple email addresses for each user.
I need to flatten this out to columns on a user query. To give me the "newest" 3 email addresses based on the creation date.
user.name | user.id | email1 | email2 | email3**
Mary | 123 | firstname.lastname@example.org | email@example.com | firstname.lastname@example.org
Joe | 345 | email@example.com | [NULL] | [NULL]
crosstab() from the tablefunc module.
SELECT * FROM crosstab( $$SELECT user_id, user_name, rn, email_address FROM ( SELECT u.user_id, u.user_name, e.email_address , row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST) AS rn FROM usr u LEFT JOIN email_tbl e USING (user_id) ) sub WHERE rn < 4 ORDER BY user_id $$ , 'VALUES (1),(2),(3)' ) AS t (user_id int, user_name text, email1 text, email2 text, email3 text);
I used dollar-quoting for the first parameter, which has no special meaning. It's just convenient if you have to escape single quotes in the query string which is a common case:
Detailed explanation and instructions here:
And in particular, for "extra columns":
The special difficulties here are:
The lack of key names.
-> We substitute with
row_number() in a subquery.
The varying number of emails.
-> We limit to a max. of three in the outer
crosstab() with two parameters, providing a list of possible keys.
Pay attention to
NULLS LAST in the