copying data from 1:N relationship into single table (effectively)
The second "proposal" is probably the way to do this (using conditional aggregation to pivot). But it's a little different than what you have in your question:
INSERT INTO flat ( id /* , other columns */, key1, key2, key3 )SELECT id -- , other columns , MAX(CASE WHEN key = 'key1' THEN val END) AS key1 -- ELSE NULL is superfluous , MAX(CASE WHEN key = 'key2' THEN val END) AS key2 , MAX(CASE WHEN key = 'key3' THEN val END) AS key3 FROM item i JOIN property p ON i.id = p.oneFKGROUP BY i.id;That is, I assume you want the values stored in the val column and not the key indexes stored in the key column. By the way, I hope you don't have a column named key as that is a reserved word.
Hope this helps. This should work at least in both Oracle and Postgres.