title: SQL Parent Child and Inheritance
body: <p>I am using SQL Server 2016 and I'm hoping someone can help me crack this nut.</p>
  
  <p>I have a table that holds a relationship between identical records, and their properties. This is a sample table.</p>
  
  <pre><code>CREATE TABLE [Family] ([Id] Int NOT NULL, [Relation] VARCHAR(12), [Level] TinyInt,  [Shoes] VARCHAR(12), [Shirt] VARCHAR(12), [Coat] VARCHAR(12))
  
  INSERT INTO [Family] ([Id], [Relation], [Level], [Shoes], [Shirt], [Coat])
     VALUES (1, 'Grandparent', 1, 'Blue', 'Brown', 'Green'),
            (1, 'Parent',2, 'Red', NULL, NULL),
            (1, 'Child', 3, NULL, 'Yellow', NULL),
            (2, 'Grandparent', 1, 'Purple', 'Grey', 'Blue'),
            (2, 'Parent',2, NULL, 'Brown', 'Green')
            (2, 'Child', 3, NULL, NULL, 'Yellow');
  </code></pre>
  
  <p>The key fields are Id and a Level. The rule is that I want to be able to select and return one row per id where the property returned it the one with the highest level that is not null. i.e if the value is null it is inherited from the next lower level.</p>
  
  <p>I hope I explained that right, so the results would look like this:</p>
  
  <pre><code>Id          Shoes  Shirt  Coat
  ----------- ------ ------ ------
  1           Red    Yellow Green
  2           Purple Brown  Yellow
  </code></pre>
  
  <p>I have tried a few ways with <code>ISNULL</code> and functions, but they end up a mess and are slow. Of course what I have posted here is a simple example, the real table would have 8-10 levels and 20+ columns of properties, and I need it to perform over a large dataset.</p>
  
  <p>I have had a hunt around and not found anything really matches, thank you</p>