SQL Server 2008 R2 - select hierarchical data

Question

I have a table in SQL Server which holds categories and sub-categories. They are connected by a relation between ID and PID.

Top level items have a PID of 0 and other rows have the PID of their parents.

What would be the most efficient way to get this data?

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).

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).

Answer 1

Assuming at least SQL Server 2005 I'd use a single query against a recursive common table expression.