title: SQL Server - Breadcrumb Type Effect
body: <p>I have a set of data such as the following:</p>
  
  <pre><code>ID, Description, ParentID
  1,  Savoury, -1
  2,  Cheese, 1
  3,  Pastry, 1
  4,  Quiche, 1
  5,  Sweet, -1
  6,  Chocolate, 5
  ...
  </code></pre>
  
  <p>What I'm looking to do is write a stored procedure which will produce a set of results similar to a breadcrumb link in a web browser. Something like the following would be ideal...</p>
  
  <pre><code>ID, BreadCrumb
  1, Savoury
  2, Savoury &gt;&gt; Cheese
  3, Savoury &gt;&gt; Pastry
  4, Savoury &gt;&gt; Quiche
  5, Sweet
  6, Sweet &gt;&gt; Chocolate
  ...
  </code></pre>
  
  <p>Items such as Cheese or Pastry wouldn't be listed as items on their own (like Sweet is). So far, I have the following code which works but it lists everything, regardless of whether it has a ParentID</p>
  
  <pre><code>With BreadCrumb AS 
  (
  SELECT CAST(a.Description AS VARCHAR(100)) AS Path, a.ID, a.ParentID
  FROM FoodStuff a
  
  UNION ALL
  
  SELECT CAST(BreadCrumb.Path + ' &gt;&gt; ' + b.Description AS VARCHAR(100)) AS Path, b.ID, b.ParentID
  FROM FoodStuff b
  INNER JOIN BreadCrumb ON BreadCrumb.ID = b.ParentID
  
  )
  
  SELECT * FROM BreadCrumb
  ORDER BY Path
  </code></pre>
  
  <p>I'd be grateful for a nudge in the right direction.</p>
  
  <p>Thanks in advance,
  Kev</p>