title: SQL Query: Hierarchical Coalesce
body: <p>I have a table that defines a hierarchy:</p>
  
  <pre><code>Create Table [example] (
      id          Integer   Not Null Primary Key,
      parentID    Integer       Null,
      largeData1  nVarChar(max) Null,
      largeData2  nVarChar(max) Null);
      -- largeData3...n also exist
  
  Insert Into [example] (id, parentID, largeData1, largeData2)
  Select 1, null, 'blah blah blah', null          Union
  Select 2,    1, null,             null          Union
  Select 3,    1, 'foo bar foobar', null          Union
  Select 4,    3, null,             'lorem ipsum' Union
  Select 5,    4, null,             null;
  </code></pre>
  
  <p>Hierarchy diagram for this data:</p>
  
  <p><img src="https://i.stack.imgur.com/9nkEz.png" alt="Hierarchy diagram"></p>
  
  <p>I want to write a query that will return a single row for any given [id] value. The row should contain that row's [id] and [parentID] information.  It should also contain the [largeData1...n] fields.  However, if a largeData field is null, then it should traverse up the hierarchy until a non-null value for that field is encountered.  It should, in short, function like the coalesce function, except across a hierarchy of rows instead of a set of columns.</p>
  
  <p>Example:</p>
  
  <p><strong>Where [id] = 1:</strong></p>
  
  <pre><code>id:          1
  parentID:    null
  largeData1:  blah blah blah
  largeData2:  null
  </code></pre>
  
  <p><strong>Where [id] = 2</strong></p>
  
  <pre><code>id:          1
  parentID:    1
  largeData1:  blah blah blah
  largeData2:  null
  </code></pre>
  
  <p><strong>Where [id] = 3</strong></p>
  
  <pre><code>id:          3
  parentID:    1
  largeData1:  foo bar foobar
  largeData2:  null
  </code></pre>
  
  <p><strong>Where [id] = 4</strong></p>
  
  <pre><code>id:          4
  parentID:    3
  largeData1:  foo bar foobar
  largeData2:  lorem ipsum
  </code></pre>
  
  <p><strong>Where [id] = 5</strong></p>
  
  <pre><code>id:          5
  parentID:    4
  largeData1:  foo bar foobar
  largeData2:  lorem ipsum
  </code></pre>
  
  <p>So far, I have this:</p>
  
  <pre><code>Declare @id Integer; Set @id = 5;
  
  With heirarchy
      (id, parentID, largeData1, largeData2, [level])
  As (
      Select id, parentID, largeData1,
             largeData2, 1 As [level]
      From example
      Where id = @id
  
      Union All
  
      Select parent.id, parent.parentID,
             parent.largeData1,
             parent.largeData2,
             child.[level] + 1 As [level]
      From example As parent
      Inner Join heirarchy As child
          On parent.id = child.parentID)
  
  Select id, parentID,
     (Select top 1 largeData1
      From heirarchy
      Where largeData1 Is Not Null
      Order By [level] Asc) As largeData1,
  
     (Select top 1 largeData2
      From heirarchy
      Where largeData2 Is Not Null
      Order By [level] Asc) As largeData2
  
  From example
  Where [id] = @id;
  </code></pre>
  
  <p>This returns the results that I am looking for.  However, according to the query plan, it is making a separate pass through the hierarchy for each largeData field that I pull back.</p>
  
  <p><strong>How can I make this more efficient?</strong></p>
  
  <p>This is obviously a simplified version of a more complex problem.  The final query will return data in XML format, so any solutions involving the FOR XML clause are perfectly fine.</p>
  
  <p>I can create a CLR aggregate function for this, if doing so would help.  I have not yet explored that route.</p>