Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t
AS
SELECT '2021-01-01'::DATE, 1 Amount1, 2 Amount2, 3 Amount3 
UNION ALL SELECT '2021-01-02'::DATE, 1 Amount1, 3 Amount2, 2 Amount3 
UNION ALL SELECT '2021-01-03'::DATE, 2 Amount1, 4 Amount2, 1 Amount3 
UNION ALL SELECT '2021-01-04'::DATE, 3 Amount1, 5 Amount2, 2 Amount3 
4 rows affected

SELECT *
FROM t


date
amount1
amount2
amount3


2021-01-01
1
2
3

2021-01-02
1
3
2

2021-01-03
2
4
1

2021-01-04
3
5
2


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


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


-- simulating new column over time
ALTER TABLE t ADD COLUMN Amount4 INT DEFAULT 5
SELECT t.date, s3."key", s3."value"
FROM t
,LATERAL (SELECT *
          FROM (SELECT row_to_json(t.*)) s(c)
          ,LATERAL JSON_EACH(s.c) s2
          WHERE s2."key" LIKE 'amount%'
         ) s3


date
key
value


2021-01-01
amount1
1

2021-01-01
amount2
2

2021-01-01
amount3
3

2021-01-01
amount4
5

2021-01-02
amount1
1

2021-01-02
amount2
3

2021-01-02
amount3
2

2021-01-02
amount4
5

2021-01-03
amount1
2

2021-01-03
amount2
4

2021-01-03
amount3
1

2021-01-03
amount4
5

2021-01-04
amount1
3

2021-01-04
amount2
5

2021-01-04
amount3
2

2021-01-04
amount4
5

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