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?

You could use UNION ALL

Lukasz Szozda
– Lukasz Szozda

2019-07-03 21:07:48 +00:00
Commented Jul 3, 2019 at 21:07 — Lukasz Szozda
– Lukasz Szozda, Commented Jul 3, 2019 at 21:07

Erwin Brandstetter · Accepted Answer · 2022-03-27 23:30:10Z

2

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)
, ...

Postgres: convert single row to multiple rows (unpivot)

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

PostgreSQL Crosstab Query

edited Mar 27, 2022 at 23:30

answered Jul 3, 2019 at 21:32

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related