Optimize query collecting all related items
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              |
+------------+--------------------+