Un-Pivoting Postgres for a large number of columns

Question

So I've been looking at crosstab for pivoting but not sure if there is a more scalable way to do this.

Right now I have a structure that looks like

Date	Amount1	Amount2	Amount3
Date	1	2	1
Date	1	3	2
Date	2	4	1
Date	3	5	2

I'd like to ideally get it into this format

Date	Name	Amount
Date	Amount1	1

etc etc

Now the problem I have is that the Amount1 can be dynamic and can keep increasing over time. I'm trying to not have to hard code it as there's about 40 columns right now and I can see it increasing over time

Answer 1

Yes, it is possible without hardcoding anything except column prefix:

SELECT t.date, s3."key" as name, s3."value" as amount
FROM t
,LATERAL (SELECT *
          FROM (SELECT ROW_TO_JSON(t.*)) s(c)
          ,LATERAL JSON_EACH(s.c) s2
          WHERE s2."key" LIKE 'amount%') s3;

db<>fiddle demo

Output:

+-------------+----------+-------+
|    date     |   key    | value |
+-------------+----------+-------+
| 2021-01-01  | amount1  |     1 |
| 2021-01-01  | amount2  |     2 |
| 2021-01-01  | amount3  |     3 |
| 2021-01-02  | amount1  |     1 |
| 2021-01-02  | amount2  |     3 |
| 2021-01-02  | amount3  |     2 |
| 2021-01-03  | amount1  |     2 |
| 2021-01-03  | amount2  |     4 |
| 2021-01-03  | amount3  |     1 |
| 2021-01-04  | amount1  |     3 |
| 2021-01-04  | amount2  |     5 |
| 2021-01-04  | amount3  |     2 |
+-------------+----------+-------+

How it works:

1. Generate json from row
2. Parse json and choose only values that key has specific prefix

EDIT: (by gordon)

I don't see a need for the subquery. The query can be simplified to:

SELECT t.date, je.key, je.value
FROM t cross join lateral
     row_to_json(t.*) rtj(r) cross join lateral
     JSON_EACH(rtj.r) je
WHERE je."key" LIKE 'amount%';