I am trying to get the category tree from my site but I am not that familiar with recursion. I am using sql2005. I was able to get some of it but then I got lost. I was trying to get root categories, then root categories"\" sub-category and so on until all categories are listed. Posted is the code I have so far and an image of what I want it to look like when it is done. The image is just for one root i have severel root categories. Thanks for all your help.
WITH temp_catChart (name, ParentCategoryID, categoryid, iteration) AS
SELECT name, ParentCategoryID, categoryid,0
FROM category WHERE ParentCategoryID = 0
UNION ALL SELECT b.name,a.categoryid, b.categoryid, a.iteration + 1
FROM temp_catChart AS a, category AS b
WHERE a.categoryid = b.ParentCategoryID
SELECT name, categoryid,ParentCategoryID, iteration