sql - Postgres - Transpose Rows to Columns -

i have following table, gives multiple email addresses each user.

i need flatten out columns on user query. give me "newest" 3 email addresses based on 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] 

use crosstab() tablefunc module.

select * crosstab(    $$select user_id, user_name, rn, email_address       (         select u.user_id, u.user_name, e.email_address              , row_number() on (partition u.user_id                             order e.creation_date desc nulls last) rn           usr u         left   join email_tbl e using (user_id)         ) sub       rn < 4      order  user_id    $$   , 'values (1),(2),(3)'    ) t (user_id int, user_name text, email1 text, email2 text, email3 text); 

i used dollar-quoting first parameter, has no special meaning. it's convenient if have escape single quotes in query string common case:

• insert text single quotes in postgresql

detailed explanation , instructions here:

• postgresql crosstab query

and in particular, "extra columns":

• pivot on multiple columns using tablefunc

the special difficulties here are:

• the lack of key names.
  -> substitute row_number() in subquery.

• the varying number of emails.
  -> limit max. of 3 in outer select
  , use crosstab() 2 parameters, providing list of possible keys.

pay attention nulls last in order by.