title: SQL Server 2008 R2 - select hierarchical data
body: <p>I have a table in SQL Server which holds categories and sub-categories. They are connected by a relation between <code>ID</code> and <code>PID</code>. </p>
  
  <p>Top level items have a <code>PID</code> of 0 and other rows have the <code>PID</code> of their parents. </p>
  
  <p>What would be the most efficient way to get this data?</p>
  
  <p>The naive algorithm for this would be to loop through the list of parents and then get the children for each parent in a different query (either against the DB or the dataset).</p>
  
  <p>Is there any methods built in to the framework to support a better way of doing this? Something that will allow me to easily bind to a repeater (or other data control).</p>