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