sql - 将具有多列的单行转置为两列的多行

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() 函数为此工作。我能做什么？

Answer 1

您需要crosstab() 所做的reverse 操作。有人称之为“逆向”。 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: convert single row to multiple rows (unpivot)

对于反向操作——“透视”或“交叉制表”:

• PostgreSQL Crosstab Query