Parent Child Hierarchy CTE query

  • Hi,

    I have managed to construct a CTE query to get a hierachy of parent-child related items from my table.

    (not quite understanding the plot entirely but following examples)

    In the query that is returned there is a column 'MenuText' which is the MenuText of the child item

    I need to have also a column returned as 'ParentMenuText', which is from the same MenuText column as the childs MenuText.

    AMIIDMenuTextParentIDDisplayOrderURLToolTipDisplayNewWindow

    1Top LevelNULL1NULLNULLFalseFalse

    3Home11Default.aspxNULLTrueFalse

    So that for the row of the child with AMIID of 3 ParentID = 1 and ParentMenuText = Top Level

    I can't work out how to do it.

    Any help appreciated and any pointers to good TSQL tutorials and books also appreciated as I need to do some (a lot) of learning.

    This is my CTE uery

    WITH Hierarchy(ParentID, AMIID, MenuText, level, DisplayOrder, URL, ToolTip, Display, NewWindow, SortKey)

    AS ( SELECT ParentID, AMIID, MenuText, 0, DisplayOrder, URL, ToolTip, Display, NewWindow,

    CAST ('' AS VARCHAR(MAX)) FROM MenuItems

    WHERE ParentID IS NULL UNION ALL

    SELECT e.ParentID, e.AMIID, e.MenuText, level + 1, e.DisplayOrder, e.URL, e.ToolTip, e.Display, e.NewWindow, d.SortKey +

    case

    when Len(e.DisplayOrder) = 0 then '00' + CAST(e.DisplayOrder as VARCHAR) + '.'

    when Len(e.DisplayOrder) = 1 then '0' + CAST( e.DisplayOrder as VARCHAR) + '.'

    when Len(e.DisplayOrder) = 2 then CAST(e.DisplayOrder as VARCHAR) + '.' End FROM MenuItems e

    INNER JOIN Hierarchy d ON e.ParentID = d.AMIID )

    SELECT ParentID, AMIID, MenuText as MenuText, level, DisplayOrder,URL, ToolTip, Display, NewWindow, SortKey

    FROM Hierarchy ORDER BY SortKey

    Cheers

  • Try this

    With Hierarchy (ParentID, AMIID, MenuText, ParentMenuText, RootMenuText, level, DisplayOrder, URL, ToolTip, Display, NewWindow, SortKey)

    As (Select ParentID, AMIID, MenuText, MenuText, MenuText, 0, DisplayOrder, URL, ToolTip, Display, NewWindow, Cast ('' As Varchar (Max))

    From MenuItems

    Where ParentID Is Null

    Union All

    Select e.ParentID, e.AMIID, e.MenuText, d.MenuText, d.RootMenuText, level + 1, e.DisplayOrder, e.URL, e.ToolTip, e.Display, e.NewWindow, d.SortKey + Case When Len(e.DisplayOrder) = 0 Then '00' + Cast (e.DisplayOrder As Varchar) + '.' When Len(e.DisplayOrder) = 1 Then '0' + Cast (e.DisplayOrder As Varchar) + '.' When Len(e.DisplayOrder) = 2 Then Cast (e.DisplayOrder As Varchar) + '.' End

    From MenuItems As e

    Inner Join

    Hierarchy As d

    On e.ParentID = d.AMIID)

    Select ParentID, AMIID, MenuText As MenuText, level, DisplayOrder, URL, ToolTip, Display, NewWindow, SortKey

    From Hierarchy

    Order By SortKey;

    That will give you, for each item, the menu text, the menu text of the parent item, and the menu text of the root item.

    Hope that helps.

    Atlantis Interactive - SQL Server Tools
    My blog[/url]
    Why I wrote a sql query analyzer clone

  • Oh Luxury!

    Many thanks, several hours (or days) more of headbanging averted and am in with a chance of hitting my deadline.

    It's also helping my understanding of how to work the CTEs and hierarchies.

    A good example saves so much time.

    Can't thank you enough!

    :-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-)

Viewing 3 posts - 1 through 2 (of 2 total)

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