Home Forums SQL Server 2008 T-SQL (SS2K8) Need Help Deconstructing Tree Hierarchy Adjacency menu Table with T-SQL RE: Need Help Deconstructing Tree Hierarchy Adjacency menu Table with T-SQL

  • 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".