How to create temporal table from current and history?-CodePudding

Let's say I have a current table that looks like this:

location   status     price
A          sold       3

and I have a history table that looks as such:

location   field     oldval_str   newval_str   oldvar_num   newval_num   created_at
A          status    closed       sold         null         null         2022-06-01
A          status    listed       closed       null         null         2022-05-01
A          status    null         listed       null         null         2022-04-01
A          price     null         null         null         1            2022-04-01
A          price     null         null         1            2            2022-05-01
A          price     null         null         2            3            2022-06-01

How can I build a temporal table such as the following in pure SQL?

location   status     price   created_at
A          listed     1       2022-04-01
A          closed     2       2022-05-01
A          sold       3       2022-06-01

I am using a PostgresDB is dialect helps!

I know I can do this in Python, I'm stuck on calling dynamically named columns using the field from history. Would like to find an all SQL solution. Any resources to point to?

CodePudding user response：

You can use a query like this

SELECT
    l.location,
    MAX(h.newval_str) FILTER (WHERE h.field = 'status') AS status,
    MAX(h.newval_num) FILTER (WHERE h.field = 'price') AS price,
    h.created_at
FROM locations l
JOIN history h ON h.location = l.location
GROUP BY l.location, h.created_at
ORDER BY h.created_at

Please, check a demo