title: How to use CTE to map parent-child relationship?
body: <p>Say I have a table of items representing a tree-like structured data, and I would like to continuously tracing upward until I get to the top node, marked by a parent_id of NULL. What would my MS SQL <strong>CTE</strong> (common table expression) look like?</p>
  
  <p>For example, if I were to get the path to get to the top from <strong>Bender</strong>, it would look like</p>
  
  <p><strong>Comedy</strong></p>
  
  <p><strong>Futurama</strong></p>
  
  <p><strong>Bender</strong></p>
  
  <p>Thanks, and here's the sample data:</p>
  
  <pre><code>DECLARE @t Table(id int, description varchar(50), parent_id int)
  
  INSERT INTO @T 
  SELECT 1, 'Comedy', null UNION 
  SELECT 2, 'Futurama', 1 UNION
  SELECT 3, 'Dr. Zoidberg', 2 UNION 
  SELECT 4, 'Bender', 2 UNION
  SELECT 5, 'Stand-up', 1 UNION
  SELECT 6, 'Unfunny', 5 UNION
  SELECT 7, 'Dane Cook', 6
  </code></pre>