Flatten audit table records into one combined record

Question

I'm working with a BigQuery database table that holds history changes to different fields as separate events in a history table.

For example, take a table that has two fields:

Id	Food	Color
6	Bacon	Red
9	Salad	Green

Any changes to the values in this table would be output to a history table like:

History table

Change date	id	Field name	Field Value
17 Jan 2022	6	Food	Steak
19 Jan 2022	9	Color	Yellow
21 Jan 2022	6	Color	Blue

How can I transform the 'history' table so that it flattens the change values into a single inherited view of change state as shown below?

Transformed table

id	Date	Food	Color
6	17 Jan 2022	Steak	Red
9	19 Jan 2022	Salad	Yellow
6	21 jan 2022	Steak	Blue

Answer 1

This requires to first PIVOT history table data then join to current data table. I use Access SQL but maybe can adapt the following:

Query1:

TRANSFORM First(History.FieldValue) AS FirstOfFieldValue
SELECT History.ChangeDate, History.ID
FROM History
GROUP BY History.ChangeDate, History.ID
PIVOT History.FieldName;

Query2:

SELECT Data.ID, Query1.ChangeDate, 
    IIf([Query1].[Color] IS NULL, [Data].[Color], [Query1].[Color]) AS C, 
    IIf([Query1].[Food] IS NULL,[Data].[Food], [Query1].[Food]) AS F
FROM Query1 INNER JOIN Data ON Query1.ID = Data.ID;

Answer 2

As per your requirement, you can use below code to get expected output.

SELECT h.id,h.DateofChange,
       MAX(IF(Field_name = "Food", Field_value, d.Food)) as Food ,
       MAX(IF(Field_name = "Color", Field_value, d.Color)) as Color 
FROM `project.dataset.history3` as h, `project.dataset.demo3` as d WHERE h.id=d.id
GROUP BY h.id,h.DateofChange

I am attaching screenshots of the result.

Demo Table

History Table

Query Results