SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parent Child Hierarchy CTE query


Parent Child Hierarchy CTE query

Author
Message
Hilary.Jenkinson
Hilary.Jenkinson
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 16
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.

AMIID MenuText ParentID DisplayOrder URL ToolTip Display NewWindow
1 Top Level NULL 1 NULL NULL False False
3 Home 1 1 Default.aspx NULL True False


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
Matt Whitfield
Matt Whitfield
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 719
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
Why I wrote a sql query analyzer clone
Hilary.Jenkinson
Hilary.Jenkinson
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 16
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!

:-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search