September 30, 2009 at 2:19 pm
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
October 1, 2009 at 2:54 am
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
October 1, 2009 at 3:13 am
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