How Can I Easily Flatten This Sql Server Hierarchy Into An Inherited Inclusive List?

I have tables (for simplicity sake) as outlined below: Category -------------------- CategoryId (0 for root nodes) ParentCategoryId ProductCategory -------------------- ProductId

Solution 1:

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

WITH X (ProductId, CategoryId) AS (
    SELECT ProductId, CategoryId FROM #ProductCategory
    UNIONALLSELECT X.ProductId, C.ParentCategoryId FROM X
    INNERJOIN #Category C ON X.CategoryId = C.CategoryId
)
SELECT ProductId, CategoryId FROM X ORDERBY CategoryId, ProductId

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