2

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

GMB
  • 216,147
  • 25
  • 84
  • 135
Nicholas Tong
  • 105
  • 8

1 Answers1

4

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%';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    You sir are a genius. I have one small question - how do I semantically add in a where clause after the FROM t bit? I'd like to filter out WHERE some_var = 'abc' for example – Nicholas Tong Dec 29 '20 at 10:57
  • @NicholasTong Glad to hear I could help. Additional `WHERE` goes after s3 like `SELECT ... FROM t LATERAL (...) s3 WHERE t.some_var = 'abc'` – Lukasz Szozda Dec 29 '20 at 11:00
  • Ahh sorry very last question - the number is being returned is in json but I can't seem to cast it normally like amount::double precision. I looked up using ->> notation but I don't think this is correct or works? – Nicholas Tong Dec 29 '20 at 11:10
  • @NicholasTong: use `json_each_text()` instead of `json_each()` –  Dec 29 '20 at 12:22
  • 1
    @GordonLinoff Thank you for edit Yes, of course you are right, the subquery is leftover of my first try with `JSON_OBJECT_AGG` ;) – Lukasz Szozda Dec 29 '20 at 16:05