clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601453 fiddles created (47971 in the last week).

CREATE TABLE source (hierarchy VARCHAR(8), setting INT, "option" INT, preference VARCHAR(8), color VARCHAR(8))
 hidden batch(es)


INSERT INTO source VALUES ('100',NULL,1,NULL ,NULL), ('10' ,NULL,2,'square' ,NULL), ('1' ,0 ,3,'rounded','green')
3 rows affected
 hidden batch(es)


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 '_'
setting option preference color
0 1 square green
 hidden batch(es)