SQL Postgres - 将行转置为列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23060256/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres - Transpose Rows to Columns
提问by dacology
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.
我需要将其展平为用户查询的列。根据创建日期给我“最新”的 3 个电子邮件地址。
user.name | user.id | email1 | email2 | email3**
Mary | 123 | [email protected] | [email protected] | [email protected]
Joe | 345 | [email protected] | [NULL] | [NULL]
回答by Erwin Brandstetter
Use crosstab()from the tablefuncmodule.
使用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);
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:
我对第一个参数使用了美元引用，它没有特殊含义。如果您必须在查询字符串中转义单引号，这很方便，这是一种常见情况：
Detailed explanation and instructions here:
详细解释和说明在这里：
And in particular, for "extra columns":
特别是，对于“额外的列”：
The special difficultieshere are:
这里的特殊困难是：
The lack of key names.
-> We substitute withrow_number()in a subquery.The varying number of emails.
-> We limit to a max. of three in the outerSELECT
and usecrosstab()with two parameters, providing a list of possible keys.
缺少关键名称。
-> 我们row_number()在子查询中替换。不同数量的电子邮件。
-> 我们限制为最大值。三个在外部SELECT
并crosstab()与两个参数一起使用，提供可能的键列表。
Pay attention to NULLS LASTin the ORDER BY.
回答by Hannes Landeholm
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
如果其他人发现这个问题并需要一个动态解决方案，其中您有未定义数量的列要转置而不是 3，您可以在这里找到一个很好的解决方案：https: //github.com/jumpstarter-io/轴心
