将单行多列转置为两列多行。

Question

我有一个SELECT查询，它工作得非常好，并返回一个带有多个命名列的单行记录：

| registered | downloaded | subscribed | requested_invoice | paid |
|------------|------------|------------|-------------------|------|
| 9000       | 7000       | 5000       | 4000              | 3000 |

但我需要将这个结果转置到一个新表格中，新表格应该如下所示：

| type              | value |
|-------------------|-------|
| registered        | 9000  |
| downloaded        | 7000  |
| subscribed        | 5000  |
| requested_invoice | 4000  |
| paid              | 3000  |

我已经在PostgreSQL上启用了额外的模块tablefunc，但我无法使用crosstab()函数。我该怎么办？

- prm

---

你可以使用 UNION ALL。 - Lukasz Szozda

Answer 1

你需要进行逆操作，即crosstab()的反向操作。有些人称其为“unpivot”。使用LATERAL连接到一个VALUES表达式应该是最优雅的方法：

SELECT l.*
FROM   tbl     --  or replace the table with your subquery
CROSS  JOIN LATERAL (
   VALUES
  ('registered'       , registered)
, ('downloaded'       , downloaded)
, ('subscribed'       , subscribed)
, ('requested_invoice', requested_invoice)
, ('paid'             , paid)
   ) l(type, value)
WHERE  id = 1;  --  or whatever

你可能需要将一些或所有列转换为相同的数据类型。例如：

最初的回答：

...
   VALUES
  ('registered'       , registered::text)
, ('downloaded'       , downloaded::text)
, ...

相关：

• Postgres：将单行转换为多行（解枢）

对于反向操作 - “枢轴”或“交叉表”：

• PostgreSQL交叉表查询

最初的回答：这些链接提供了有关如何在PostgreSQL中进行数据重组的有用信息。第一个链接演示了如何将单行数据转换为多行，而第二个链接演示了如何将数据从行透视到列（即“枢轴”或“交叉表”）。

- Erwin Brandstetter