4

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
2
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              |
+------------+--------------------+
  • Nice solution @Dudu Markvitz. Will it work end depth? – vicky Mar 2 '17 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) – David דודו Markovitz Mar 2 '17 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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