Un-Pivoting Postgres for a large number of columns

user_8486191 0
Viewed 55

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

lateral-join unpivot postgresql sql
1 Answers

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%';
user_50708790
Related