SQL Server 2008 R2 - select hierarchical data

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 na开发者_如何学Pythonive 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).

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

You could use Comman Table Expression to get the hierarchical data in Sql Server.