Optimize query collecting all related items

Question

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

union
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

union
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, 
  STUFF(
         (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

Answer 1

with    cte
        as
        (
            select      CategoryID  as root_CategoryID
                       ,1           as lvl
                       ,CategoryID


            from        TB_CATEGORY_MASTER

            where       ParentID is null


            union all


            select      c.root_CategoryID
                       ,c.lvl + 1
                       ,t.CategoryID

            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              |
+------------+--------------------+