I have two tables (simplified).
table1: - id (serial) table2: - table1_id (integer) - value (text) - type (integer)
I want to join these two tables, but depending on the type, I want to join the "value" of table2 into different columns. Result should look like this:
id | table2value0 | table2value1 | table2othervalues
table2value0 is the value from
type is 0.
table2value1 is the value from
type is 1.
table2othervalues are the remaining values where
type is something else, as an array.
If there are more than one value for
table2value1, I only want the first. Order doesn't matter, so the order it was inserted.
If some entries in table1 do not have matches in table2, they should appear in the output as NULL.