How to create temporal table from current and history?

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?

Answer

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
LEFT JOIN history h ON h.location = l.location
GROUP BY l.location, h.created_at
ORDER BY h.created_at

Please, check a demo

Here the locations table is joined with the history table to get all of the available location rows, even those which values was not ever changed, and the history table does not contain any row about them.

If you do not need it, just use the history table alone.

 SELECT
    location,
    MAX(newval_str) FILTER (WHERE field = 'status') AS status,
    MAX(newval_num) FILTER (WHERE field = 'price') AS price,
    created_at
FROM history
GROUP BY location, created_at
ORDER BY created_at