Sql Postgres 将行转置为列

发布于04月14日

下表给出了每个用户的多个邮箱地址.

在此处输入图像描述

我需要在用户查询中将其平铺为列.根据创建日期给我"最新"的3个邮箱地址.

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]

推荐答案

使用tablefunc模块中的crosstab().

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

我对第一个参数使用了美元报价，它没有特殊意义.在查询字符串中转义单引号很方便，这是一种常见情况:

Insert text with single quotes in PostgreSQL

详细解释和说明:

PostgreSQL Crosstab Query

特别是对于"额外列":

Pivot on Multiple Columns using Tablefunc

这里的special difficulties个是:

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.

注意NULLS LAST in the ORDER BY.