sqldatabasepostgresqlpivot-tabletranspose

Transpose latest rows per user to columns


I have the following table, which gives multiple email addresses for each user.

table sample

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. So the expected output looks like:

user_name user_id email1 email2 email3
Mary 123 [email protected] [email protected] [email protected]
Joe 345 [email protected] [NULL] [NULL]

Solution

  • 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:

    Detailed explanation and instructions:

    And in particular, for "extra columns":

    The special difficulties here are:

    • The lack of key values.
      ā†’ 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.