title: How can I generate a hierarchy path in SQL that leads to a given node?
body: <p>In my MS SQL 2008 R2 database I have this table: </p>
  
  <pre><code>TABLE [Hierarchy]
  [ParentCategoryId] [uniqueidentifier] NULL,
  [ChildCategoryId] [uniqueidentifier] NOT NULL
  </code></pre>
  
  <p>I need to write a query that will generate all paths that lead to a given Node. </p>
  
  <p>Lets say it I have the following tree:</p>
  
  <pre><code>A
  -B
  --C
  -D
  --C
  </code></pre>
  
  <p>Which would be stored as:</p>
  
  <pre><code>NULL | A
  A    | B
  A    | D
  B    | C
  D    | C
  </code></pre>
  
  <p>When asking for the Paths for C, I would like to get back two paths (written more or less like this): </p>
  
  <pre><code>A &gt; B &gt; C,
  A &gt; D &gt; C
  </code></pre>