How to do SQL recursion for hierarchy on categories

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
FROM temp_catChart

Open in new window

cat-tree.jpg
pgraemeAsked:
Who is Participating?
 
ralmadaCommented:
try this
with CTE as ( 
select  convert(varchar(max), name) as name,  
                categoryid,  
                ParentCategoryID,  
                0 as iteration
from category  
where ParentCategoryID = 0 
union all 
select  convert(varchar(max), d.name + '/' + a.name),  
                a.categoryid,  
                a.ParentCategoryID,  
                d.iteration + 1 
from category a 
inner join CTE d on d.categoryid = a.parentCategoryID
) 
SELECT name, iteration
from CTE
where iteration > 0

Open in new window

0
 
Paul_Harris_FusionCommented:
Hi  - the following is a previously posted solution which is very similar to your requirement

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_25781637.html


The following function is adapted from Example C at
http://msdn.microsoft.com/en-us/library/ms186755.aspx

CREATE FUNCTION dbo.FindChildren (@Id INTEGER)
RETURNS @results TABLE
(
    Id int primary key NOT NULL,
    ParentId int ,
    HierarchyLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH resultsTable ( Id, ParentId, HierarchyLevel) AS
      (SELECT Id, ParentId, 1 from temp t WHERE t.Id = @Id
     UNION ALL
       SELECT t.Id, t.ParentId, r.HierarchyLevel + 1
     from temp t inner Join resultsTable r on t.ParentId = r.Id
    )

-- copy the required columns to the result of the function
   INSERT @results
   SELECT Id, ParentId, HierarchyLevel
   FROM resultsTable
   RETURN
END;


My test table is called temp so you would need to change that for your own schema and column names

To return the results...

Select * from FindChildren(1)
0
 
pgraemeAuthor Commented:
I am looking to get this result set:
/Business Electronics
/Business Electronics/Office Machines
/Business Electronics/Office Machines/Copiers
/Business Electronics/Office Machines/Copiers/Hardware
/Business Electronics/Office Machines/Copiers/Ink & Toner
/Cables
/Cables/Power
/Cables/Power/2 Pole IEC-C7
/Cables/Power/3 Pole IEC-C15
/Car Electronics
/Car Electronics/Audio
/Car Electronics/Audio/Amplifiers
/Car Electronics/Audio/Amplifiers/2 Channel
/Computers
/Computers/Printers
/Computers/Printers/Accessories
/Computers/Printers/Dot-Matrix

Hopefully this helps in the understanding of what I need.  Thanks.
0
 
pgraemeAuthor Commented:
That was perfect.  I only had to modify it to include the root category name.  Thanks for all your help.
with CTE as (  
select  convert(varchar(max), name) as name,   
                categoryid,   
                ParentCategoryID,   
                0 as iteration 
from category   
where ParentCategoryID = 0  
union all  
select  convert(varchar(max), d.name + '/' + a.name),   
                a.categoryid,   
                a.ParentCategoryID,   
                d.iteration + 1  
from category a  
inner join CTE d on d.categoryid = a.parentCategoryID 
)  
SELECT name
from CTE 
where iteration > 0 
union all
select name from category
where ParentCategoryID = 0
order by name

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.