Transpose latest rows per user to columns

by

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

More Related Contents:

  1. PostgreSQL Crosstab Query
  2. Refactor a PL/pgSQL function to return the output of various SELECT queries
  3. How to check if a table exists in a given schema
  4. Dynamic alternative to pivot with CASE and GROUP BY
  5. How to update selected rows with values from a CSV file in Postgres?
  6. Optimise PostgreSQL for fast testing
  7. Pivot on Multiple Columns using Tablefunc
  8. How to delete duplicate rows without unique identifier
  9. Best way to check for “empty or null value”
  10. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  11. PostgreSQL parameterized Order By / Limit in table function
  12. How can I send email from PostgreSQL trigger?
  13. NOT NULL constraint over a set of columns
  14. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  15. Query that ignore the spaces
  16. How to find a table having a specific column in postgresql
  17. Continuing a transaction after primary key violation error
  18. INSERT COMMAND :: ERROR: column “value” does not exist
  19. PostgreSQL convert columns to rows? Transpose?
  20. Use email address as primary key?
  21. Bulk/batch update/upsert in PostgreSQL
  22. SQL query to find record with ID not in another table
  23. Postgres Error: More than one row returned by a subquery used as an expression
  24. Grant privileges for a particular database in PostgreSQL
  25. PostgreSQL 9.3: Dynamic pivot table
  26. Upsert with a transaction
  27. How to compare dates in datetime fields in Postgresql?
  28. PostgreSQL how to create a copy of a database or schema?
  29. Temporal database design, with a twist (live vs draft rows)
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment