How to SELECT TOP(1) with COALESCE to pick the top NOT NULL row for each column?

Question

I have a hierarchy of settings that have a default for everyone, can be overridden by groups, or by an individual user, for a specific screen, etc...

These are stored in a table with a Hirarchy-ID to sort on, like this:

HIERARCHY | SETTING | OPTION | PREFERENCE | COLOR |
----------+---------+--------+------------+-------+
100       | NULL    | 1      | NULL       | NULL  |
10        | NULL    | 2      | square     |       |
1         | 0       | 3      | rounded    | green |

The result I'm after is for each column, the first not-null value (and it would be great if I can also squash a NULLIF(COLOR, '') in as well). In this example the result would be:

SETTING | OPTION | PREFERENCE | COLOR |
--------+--------+------------+-------+
0       | 1      | square     | green |

Currently I'm doing a separate query for each column but I'm hoping that this can be achieved in a more elegant way?

I'm working with MS SQL Server (and need to support version 2005).

Answer 1

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 '_'

fiddle