The following table is my menu master table that has an unlimited menu depth. Every menu can have a submenu and a sub-submenu and so on.

The problem I am facing is getting all items that have the same top-level parent.

One solution is to loop while parent is not null and second solution is to create a view with a parent id and all sub menu ids with comma separated column.

Then one select command will give the parent id. My table is as follows:

CategoryID  | ParentID    
1           | null        
2           | null        
3           | 1           
4           | 1           
5           | 2           
6           | 2           
7           | 3           

Output should be

CategoryID  | AllRelatedCategory
1           | 1,3,4,7
2           | 2,5,6

My current code is as follows. Problem is that it's working only with 3 levels of the hierarchy.

;with cte as
select a.CategoryID col1, convert(varchar, a.CategoryID) col2, a.ParentID
from [dbo].[TB_CATEGORY_MASTER] a where a.ParentID is null

select a.CategoryID col1, (convert(varchar, isnull(b.CategoryID,''))) col2, a.ParentID
from [dbo].[TB_CATEGORY_MASTER] a 
left join [dbo].[TB_CATEGORY_MASTER] b on a.CategoryID=b.ParentID

select a.CategoryID col1, (convert(varchar, isnull(c.CategoryID,''))) col2, a.ParentID
from [dbo].[TB_CATEGORY_MASTER] a 
left join [dbo].[TB_CATEGORY_MASTER] b on a.CategoryID=b.ParentID
left join [dbo].[TB_CATEGORY_MASTER] c on b.CategoryID=c.ParentID

select distinct c.col1, 
         (SELECT distinct ', ' + c1.col2
          FROM cte c1
          where c.col1 = c1.col1
          FOR XML PATH ('')), 1, 1, '') col2
from cte c
where c.ParentID is null

1 Answer 1

with    cte
            select      CategoryID  as root_CategoryID
                       ,1           as lvl

            from        TB_CATEGORY_MASTER

            where       ParentID is null

            union all

            select      c.root_CategoryID
                       ,c.lvl + 1

            from                    TB_CATEGORY_MASTER  as t

                        join        cte                 as c

                        on          c.CategoryID = t.ParentID

select     t.CategoryID

          ,(select      case when lvl = 1 then '' else ',' end 
                      + cast (c.CategoryID as varchar(10))

            from        cte as c

            where       c.root_CategoryID = t.CategoryID

            order by    lvl

            for xml path('')
            )                   as AllRelatedCategory

from        TB_CATEGORY_MASTER  as t

where       ParentID is null

| CategoryID | AllRelatedCategory |
|          1 | 1,3,4,7            |
|          2 | 2,5,6              |
  • Nice solution @Dudu Markvitz. Will it work end depth?
    – vicky
    Mar 2, 2017 at 8:46
  • Up to 100 levels. If you need more add option (maxrecursion 0) at the end the bottom of the query (Or use a number other than 0 if you want to make sure you are not stuck in an endless loop) Mar 2, 2017 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.