SQL Server 2008 R2 - Selecting Hierarchical Data

Question

I have a table on SQL Server that contains categories and subcategories. They are related by the relationship between ID and PID .

Top-level items have a PID 0, and other lines contain the PID their parents.

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

A naive algorithm for this would be to iterate over the list of parents and then get the children for each parent in another query (either against the database or data set).

Are there any methods built into the infrastructure to support a better way to do this? Something that will allow me to easily bind to a relay (or other data management).

Answer 1

Assuming at least SQL Server 2005, I would use one query against a recursive generic table expression .

Answer 2

You can use Comman Table Expression to get hierarchical data on Sql Server.