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.