How can I easily flatten this Sql Server heirarchy into an inherited inclusive list?

Question

I have tables (for simplicity sake) as outlined below:

Category
--------------------
CategoryId (0 for root nodes)
ParentCategoryId

ProductCategory
--------------------
ProductId
CategoryId

I'd like to be able to retrieve a distinct list of all categories and their inherited products (all the way back to category 0). Category 0 should include ALL products, and all other categories should follow down the hierarchy as infinitely deep as it goes.

Example Table Contents:

CategoryId, ParentCategoryId
---------------------
1, 0
2, 0
3, 0
10, 1
20, 2

ProductId, CategoryId
---------------------
1, 10
2, 1
3, 2
4, 20
5, 3

I'd like the output to travel up the heirarchy and tell me every category that a product can fall under. So a desired result would look something like this:

ProductId, CategoryId
---------------------
1, 0
2, 0
3, 0
4, 0
5, 0
1, 1
2, 1
3, 2
4, 2
5, 3
1, 10
4, 20

Is there an easy way to do this in SQL Server 2005?

Answer 1

You can do this with a recursive common table expression (cte).

WITH X (ProductId, CategoryId) AS (
    SELECT ProductId, CategoryId FROM #ProductCategory
    UNION ALL
    SELECT X.ProductId, C.ParentCategoryId FROM X
    INNER JOIN #Category C ON X.CategoryId = C.CategoryId
)
SELECT ProductId, CategoryId FROM X ORDER BY CategoryId, ProductId

More information at http://msdn.microsoft.com/en-us/library/ms186243.aspx