InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 23:39:45 GMT20http://www.sqlservercentral.com/Forums/Topic796015-338-1.aspxOh 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!:-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-):-)Thu, 01 Oct 2009 03:13:08 GMTHilary.Jenkinsonhttp://www.sqlservercentral.com/Forums/Topic796015-338-1.aspxTry this[code]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, SortKeyFrom HierarchyOrder By SortKey;[/code]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.Thu, 01 Oct 2009 02:54:03 GMTMatt Whitfieldhttp://www.sqlservercentral.com/Forums/Topic796015-338-1.aspxHi,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 itemI 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 NewWindow1 Top Level NULL 1 NULL NULL False False3 Home 1 1 Default.aspx NULL True FalseSo that for the row of the child with AMIID of 3 ParentID = 1 and ParentMenuText = Top LevelI 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 ueryWITH 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 CheersWed, 30 Sep 2009 14:19:13 GMTHilary.Jenkinson