Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parent Child Hierarchy CTE query Expand / Collapse
Author
Message
Posted Wednesday, September 30, 2009 2:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 4, 2009 8:33 AM
Points: 2, 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
Post #796015
Posted Thursday, October 1, 2009 2:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, 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
Post #796205
Posted Thursday, October 1, 2009 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 4, 2009 8:33 AM
Points: 2, 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!

Post #796211
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse