WITH cte AS ( SELECT hierarchy, setting, "option", preference, color
FROM source s1
WHERE NOT EXISTS (SELECT 1
FROM SOURCE s2
WHERE s2.hierarchy LIKE s1.hierarchy+'_')
UNION ALL
SELECT s.hierarchy,
COALESCE(cte.setting, s.setting),
COALESCE(cte."option", s."option"),
COALESCE(cte.preference, s.preference),
COALESCE(cte.color, s.color)
FROM source s
JOIN cte ON cte.hierarchy LIKE s.hierarchy+'_'
)
SELECT setting, "option", preference, color
FROM cte
WHERE hierarchy LIKE '_'
