How to pivot non-numeric columns in Snowflake?

Question

I have data that looks like this:

ID	FIELD_NAME	FIELD_VALUE	UPDATE_DATE
1	Name	Elmer Fudd	2023-01-01
1	Age	30	2023-02-01
1	Age	29	2023-03-01
2	Name	Bugs Bunny	2023-03-01
2	Name	Bugz Bunny	2023-04-01
2	Age	13	2023-04-01

I want to pivot only the most recent rows for each ID and FIELD_NAME and pivot them so the final table looks like this:

ID	NAME	AGE
1	Elmer Fudd	29
2	Bugz Bunny	13

How would you suggest I do this using Snowflake SQL?

Answer 1

In Snowflake, I would recommend a conditional max_by():

select id,
    max_by(case when field_name = 'Name' then field_value end, update_date) name,
    max_by(case when field_name = 'Age'  then field_value end, update_date) age
from mytable t
group by id