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:

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:

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).

  • 1
    Great question... multi level / multi attribute inheritance is something that has been puzzling me for a decade or so, but never had a pressing need to implement. Aug 14, 2019 at 17:56

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


  • thanks, that works, I'll have to chew on it for a while though... it looks like you are iterating from top down and the coalesce() will stick to the first not-null item it finds... but I'm having difficulty wrapping my head around the recursive UNION ALL (never thought SQL server could handle self referencing stuff), and the string concatenation of hierarchy is also tricky... I'm surprised you can come up with this so fast! I'll be back to accept the answer after I fully understand it. Aug 14, 2019 at 14:15
  • 1
    @LouisSomers I think that you have chosen a very bad scheme of hierarchy storage. It is convenient for displaying, but completely inconvenient for SQL processing. If possible, I would recommend going to the standard parent-child storage scheme, and building FQPath in the query if required. This will greatly simplify the processing. Now to identify the subordination you need to use LIKE - this is not the fastest procedure, and it uses indexes inefficiently. In addition, you have a limited maximum number of subordinate records, because you have only one character per level.
    – Akina
    Aug 15, 2019 at 5:26
  • Thanks again. I learned allot from your answer, about CTE's and the WITH clause. To tell the truth I dramatically simplified my question by using the "user rights" example. I have encountered this problem more than once. In most cases the schema is carved in stone. An example is finding the best supplier given a quantity of products, or determining the retail price of a product depending on discounts linked to customers, specific websites, the quantity etc... Sometimes I wish retailers would save my salary to give everyone a fixed low price so I could move on to doing something more useful ;-) Aug 15, 2019 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.