title: What is the fastest/easiest way to denormalize this heirarchical table into a flat table?
body: <p>I have the following hierarchical table:</p>
  
  <pre><code>Table Category:
  CategoryId, ParentCategoryId, CategoryName
  1, null, SomeRoot
  2, 1, SomeChild
  3, 2, SomeGrandchild
  4, 3, SomeGreatGrandchild
  </code></pre>
  
  <p>(note this sample data doesn't include a leaf on an earlier node than level 4, but that is possible). The data will never go deeper than level 4, if that is relevant.  I'd like to transform/pivot it to this fixed 4 level display</p>
  
  <pre><code>CatId, Name1, Name2, Name3, Name4
  1, SomeRoot, null, null, null
  2, SomeRoot, SomeChild, null, null
  3, SomeRoot, SomeChild, SomeGrandchild, null
  4, SomeRoot, SomeChild, SomeGrandchild, SomeGreatGrandchild
  </code></pre>
  
  <p>I've done left outer joining to the category table 4 times, and built a huge case statement for detecting the level to use for the ID field, but that doesn't include the null rows....  Any ideas?  HELP!</p>