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       mary gmail com    mary yahoo co uk   mary test com  Joe         345       joe gmail com      NULL               NULL

User · Answer

If anyone else that finds this question and needs a dynamic solution for this where you have an undefined number of columns to transpose to and not exactly 3  you can find a nice solution here  https   github com jumpstarter-io colpivot

User · Answer

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  lt  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    Insert text with single quotes in PostgreSQL   Detailed explanation and instructions here    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

[sql] Postgres - Transpose Rows to Columns

Examples related to sql

Examples related to database

Examples related to postgresql

Examples related to crosstab

Examples related to transpose