kwalter 22547 (3/10/2013)
mickyT, Thanks for your post. I see that your solution gives me what i need (not what i asked for, but perhaps better). How do a change it run against my menu table in the database? When i change your query to this:
with cte as (SELECT MenuID,MenuDesc,ParentMenuID,Sequence, 1 as Lev
, cast(cast(Sequence as varchar(10)) + ':' + menuid as varchar(MAX)) sortPathChar
from Menu
where ParentMenuID = ''
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenuID,m.Sequence,Lev+1
, sortPathChar + '/' + cast(m.Sequence as varchar(10)) + ':' + m.menuid sortPathChar
from cte
join Menu m
on m.ParentMenuID=cte.MenuID
)
SELECT * FROM cte
ORDER BY sortPathChar
I get this error:
Lookup Error - SQL Server Database Error: Types don't match between the anchor and the recursive part in column "sortPathChar" of recursive query "cte".
Sorry I assumed that your MenuID was varchar since your ParentMenuID had an empty string. You will need to cast the menuid as varchar(10) on both sides of the recursive cte.
with cte as (
SELECT MenuID,MenuDesc,ParentMenuID,Sequence, 1 as Lev
, cast(cast(Sequence as varchar(10)) + ':' + cast(menuid as varchar(10)) as varchar(MAX)) sortPathChar
from Menu
where ParentMenuID = ''
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenuID,m.Sequence,Lev+1
, sortPathChar + '/' + cast(m.Sequence as varchar(10)) + ':' + cast(m.menuid as varchar(10)) sortPathChar
from cte
join Menu m
on m.ParentMenuID=cte.MenuID
)
SELECT * FROM cte
ORDER BY sortPathChar