Few days ago i blogged about Common Table Expression in MS SQL 2005 and how it may help you get all sub-categories from a particular point.

Using same code and just one small change, you can get the path to the current sub-category from the very top.
With cte_Categories AS

(

   Select categoryID, Category, ParentID

   From Categories

   Where categoryID = YOURID



   UNION ALL



   Select c.categoryID, c.Category, c.ParentID

   From Categories AS c

   Inner Join cte_Categories AS cv ON c.categoryID = cv.ParentID

)



Select *

From cte_Categories
I found this very helpful, hope you will too.