Postgres - Transpose Rows to Columns

Question

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     | [email protected]  | [email protected] | [email protected]  Joe       | 345     | [email protected]   | [NULL]           | [NULL] 

Answer 1

Use 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 to escape single quotes in the query string, which is a common case:

• Insert text with single quotes in PostgreSQL

Detailed explanation and instructions:

• PostgreSQL Crosstab Query

And in particular, for "extra columns":

• Pivot on Multiple Columns using Tablefunc

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 SELECT
  and use crosstab() with two parameters, providing a list of possible keys.

Pay attention to NULLS LAST in the ORDER BY.