I have a table that I cannot change that essentially has 3 columns: group_id, key, value. Each object has its own unique group_id and then the object’s attributes are added as key/value pairs. I would like to flatten this so that a view returns one row per object and the keys are column names. For example, I have the following key/values:
Group_Id | Key | Value
1 | A | B
1 | C | D
2 | A | Q
And I want a view that returns:
Id | A | C
1 | B | D
2 | Q | null
I can achieve this through a ton of left joins, but left joins are expensive and not performant. Is there an alternative to left joins that could achieve this that is more performant?
I am not a fan of the key/value table to begin with, but can’t change it.
