Transpose single row with multiple columns into multiple rows of two columns

Question

I have a SELECT query that works perfectly fine and it returns a single row with multiple named columns:

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

But I need to transpose this result to a new table that looks like this:

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

I have the additional module tablefunc enabled at PostgreSQL but I can't get the crosstab() function to work for this. What can I do?

Answer 1

You need the reverse operation of what crosstab() does. Some call it "unpivot". A LATERAL join to a VALUES expression should be the most elegant way:

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

You may need to cast some or all columns to arrive at a common data type. Like:

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

Related:

• Postgres: convert single row to multiple rows (unpivot)

For the reverse operation - "pivot" or "cross-tabulation":

• PostgreSQL Crosstab Query