title: Recursive same-table query in SQL Server 2008
body: <p>I have the following table in a SQL Server 2008 database:</p>
  
  <pre><code>Id  Name       ParentFolder
  --  ----       ------------
  1   Europe     NULL
  2   Asia       NULL
  3   Germany    1
  4   UK         1
  5   China      2
  6   India      2
  7   Scotland   4
  </code></pre>
  
  <p>ParentFolder is a FK to Id in the same table. I would like to create a view that results in something like this:</p>
  
  <pre><code>Id  Name       FullName
  --  ----       --------
  1   Europe     Europe
  2   Asia       Asia
  3   Germany    Europe/Germany
  4   UK         Europe/UK
  5   China      Asia/China
  6   India      Asia/India
  7   Scotland   Europe/UK/Scotland
  </code></pre>
  
  <p>As you can see, I need to build the FullName values by recursively using the ParentFolder relationship an arbitrary number of times until a NULL is found.</p>
  
  <p><strong>Edit.</strong> Each row in the table "knows" what other row is its parent, but does not know its absolute position in the hierarchy. For this reason, a lineage system where each row stores its absolute location in the hierarchy tree would not be appropriate.</p>
  
  <p>I am aware of the hierarchyid feature of SQL Server 2008 but, as far as I know, it only works with a fixed number of recursion levels. In my case, however, you never know how many levels you will find, and they may change from row to row.</p>
  
  <p>I have also seen similar questions to this posted here. However, I think that nobody asked about building "paths" for each row in a table. Sorry if I missed it.</p>
  
  <p>Many thanks.</p>