Postgres-将行转置为列

Question

我有下表，该表为每个用户提供了多个电子邮件地址。

我需要将其展平为用户查询中的列。请根据创建日期为我提供“最新”的3个电子邮件地址。

user.name | user.id | email1          | email2           | email3**

Mary      | 123     | [e-mail]  | [e-mail] | [e-mail]

Joe       | 345     | [e-mail]   | [NULL]           | [NULL]

Answer 1

使用crosstab()从tablefunc模块。

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);