Post reply

recursion/heirachy for a menu in t-sql

  • July 22, 2005 at 5:01 am

    #67825

    hi

    can someone help with the following question?

    to keep the question simple if i have the following table

    page (pageid, pagename, pageparentid, pageorder)

    and the table has the following data

    pageidpagenamepageparentidpageorder
    1homepagenull1
    2section111
    3section212
    4section1-122
    5section1-221

    so as a tree it would look like this (note the order of subsection1-2 and subsection1-1 is based on the pageorder column)

    --homepage

    ----section1

    ------subsection1-2

    ------subsection1-1

    ----section2

    does anyone have a recursive SP/UDF to acheive this?

    help much appreciated...

    thanks

  • July 22, 2005 at 8:28 am

    #576441

    Adam,

    the subject of Hierarchies on SQL Server is very extensive. It can be handled in many different ways an it will kind of too long to describe here. On SQL 2005 you have the ability to create recursive CTE but While you wait for the release you could read the following to get you started:

     Manipulating Hierarchies with UDFs

    Maintaining Hierarchies

    Joe Celko has been the promotor of the nested set method (he will probaly encourage you to buy his book: Trees and Hierarchies ) Though you can get a notion of what's that about here 

    and last but not leasr BOL has a chapter on "Expanding Hierarchies" that may help you also

     

     

     


    * Noel

  • July 22, 2005 at 9:20 am

    #576461

    hi noel,

    thanks for your reply, much appreciated.  however, i've read and implemented many solutions that are covered by the articles you have supplied. non of them however have any sorting ability on them...

    any other suggestions much appreciated.

    thanks

  • July 22, 2005 at 9:26 am

    #576463

    >>.. none of them however have any sorting ability <<

    I have to differ on that one In "Mainatining Hierarchies" you have a "level"  and a "path" columns therefor if you performed a query and add:

     order by level, path you get exactly what you asked for!!! 

     


    * Noel

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply