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 12»»

Need Help Deconstructing Tree Hierarchy Adjacency menu Table with T-SQL Expand / Collapse
Author
Message
Posted Saturday, March 9, 2013 3:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 12:05 PM
Points: 7, Visits: 8
I have a Menu table with a structure like this:

MenuID, MenuDesc, ParentMenuID, Sequence


The Menu is for a packaged bit of software, and i have no ability to modify the table structure. This table has 1682 rows, and a maximum of 5 levels. Sequence field is an ordering integer that is unique to each parentMenuID subset, but that can repeat for different parentMenuID subsets.

I am trying to write a query to extract this data into Excel so that the order is correct and the levels are identified.

For example, take this dataset:

MenuID,MenuDesc,Parent Menu,Sequence
100,Main Menu,[null],1
200,Sales Management,100,1
300,Customer Relationship Management,200,1
400,Setup,300,1
500,Attribute,400,1
515,Call Type,400,2
504,Competitor,400,3
410,General Operations,300,2
521,Customer/Contact Import,410,1
550,Mobile Connect Conflicts,410,2
560,Mobile Connect Sync,410,3
305,Help Desk,200,2


I need to write a query that will return the Order (that is the order of the entire expanded tree), and the level (1-5), like this:

MenuID,MenuDesc,Parent Menu,Sequence,Order,Level
100,Main Menu,[null],1,1,1
200,Sales Management,100,1,2,2
300,Customer Relationship Management,200,1,3,3
400,Setup,300,1,4,4
500,Attribute,400,1,5,5
515,Call Type,400,2,6,5
504,Competitor,400,3,7,5
410,General Operations,300,2,8,4
521,Customer/Contact Import,410,1,9,5
550,Mobile Connect Conflicts,410,2,10,5
560,Mobile Connect Sync,410,3,11,5
305,Help Desk,200,2,12,3


I am banging my head against the wall trying to get a query to give me this. Any help or ideas would be extremely appreciated!

Thanks,
Keith
Post #1428941
Posted Saturday, March 9, 2013 8:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 38, Visits: 194
Hello,

can you check using the below query
if the query is not okay,can you explain a bit about Order Column

with cte as
(select MenuID,MenuDesc,ParentMenu
,Sequence,1 as Lev
from Menus
where ParentMenu is null
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenu
,m.Sequence,Lev+1
from cte
join Menus m
on m.ParentMenu=cte.MenuID
)select MenuID,MenuDesc,ParentMenu,Sequence,ROW_NUMBER()over(order by MenuID) as [Order]
,Lev
from cte
Post #1428951
Posted Sunday, March 10, 2013 11:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 12:05 PM
Points: 7, Visits: 8
Grasshopper: I'd a couple typos on my part on field names... i changed your query (below). When i run it now, i get no errors, but I also get zero records. You are doing some things I am not familiar with... What does the "with cte as" statement do - is it defining a dataset? If so, how can you have "cte" within your with-clause?

with cte as
(select MenuID,MenuDesc,ParentMenuID,Sequence, 1 as Lev
from Menu
where ParentMenuID is null
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenuID,m.Sequence,Lev+1
from cte
join Menu m
on m.ParentMenuID=cte.MenuID
)select MenuID,MenuDesc,ParentMenuID,Sequence,ROW_NUMBER()over(order by MenuID) as [Order],Lev
from cte

The order column i want to add will simply put the menu in the correct order when fully expanded. Note that my second example above places the original data into the correct order and correctly identifies the indention-level as well. It should start with 1, and end with 1682.

My objective is to create this query to make it easy to get the menu into an excel spreadsheet. The Menu is from an ERP system, and i need to have users go through and make some decisions regarding security. The easiest way for the users to conceptualize this is in Excel, but i cannot take this Adjacent listing of the menu table and put it into a recognizable form without doing it manually.

Thanks,
Keith

Post #1428995
Posted Sunday, March 10, 2013 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 12:05 PM
Points: 7, Visits: 8
A bit more invenstigation and i realized ParentMenuID was not null, but a zero-length string. so, changing to this give me a results. The Levels look good. the Order is still off.

the problem with the orderby value of the "over" clause is how to create the correct order... MenuID does not work, and neither does Sequence or Lev or a comnination. This is how the result set should be ordered:

1st: MainMenu1 (parent='', sequence =1, Lev=1)
2nd: SubMenu1(parent = MainMenu1, sequence = 1, Lev=2)
3rd: SubSubMenu1 (parent = SubMenu1, sequence = 1, Lev=3)
4th: SubSubSubMenu1(parent = SubSubMenu1, sequence = 1, Lev=4)
5th: MenuItem1(parent = SubSubSubMenu1, sequence = 1, Lev=5)
6th: MenuItem2(parent = SubSubSubMenu1, sequence = 2, Lev=5)
...
Xth: MainMenu2 (parent='', sequence =2, Lev1)
X+1th: SubMenu2
X+2th: SubSubMenu2
etc.

Note How the "sequence" value is invoked in the ordering when the parent menus are all equivalent, begining with the first parent menu "Main Menu". Once everything with a higher indentation level (Lev) above MainMenu (Lev=1), it goes to the previous level, checks for a higher sequence value, and either that is the next row, or it goes to the next level, etc., until Parent = ''. Ordering by (parent, sequence) does not work because the MainMenu's all sort at the top of the return set. (sequence, parent) doesn't work either. there's got to be a way.

-Keith
Post #1428998
Posted Sunday, March 10, 2013 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 12:05 PM
Points: 7, Visits: 8
with cte as
(select MenuID,MenuDesc,ParentMenuID,Sequence, 1 as Lev
from Menu
where ParentMenuID = ''
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenuID,m.Sequence,Lev+1
from cte
join Menu m
on m.ParentMenuID=cte.MenuID
)select MenuID,MenuDesc,ParentMenuID,Sequence,ROW_NUMBER()
over(order by Lev) as [Order],Lev
from cte

Post #1428999
Posted Sunday, March 10, 2013 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 12:05 PM
Points: 7, Visits: 8
Uh... Grasshopper, I just realized your ID is not "Grasshopper". sorry about that.
Post #1429001
Posted Sunday, March 10, 2013 12:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 38, Visits: 194
with cte as
(select MenuID,MenuDesc,ParentMenu
,Sequence,1 as Lev
from Menus
where ParentMenu is ''
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenu
,m.Sequence,Lev+1
from cte
join Menus m
on m.ParentMenu=cte.MenuID
)select MenuID,MenuDesc,ParentMenu,
ROW_NUMBER()OVER(partition by ParentMenu order by Menuid) as [Sequence]
,ROW_NUMBER()over(order by Lev) as [Order]
,Lev
from cte

this puts the correct sequencing,sorry for this, stii I didnt get enough clarity abut order column
I see Order only as increasing number by 1 for each row,which is what produced by above query.

the above used CTE is a recursive common table expressions,which is used for representing hierarchical data like trees,graphs
more information can be found in
http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
hope this helps.
Post #1429009
Posted Sunday, March 10, 2013 1:08 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:17 PM
Points: 1,095, Visits: 3,182
Hi

Jeff Moden has this excellent article on hierarchies

http://www.sqlservercentral.com/articles/Hierarchy/94040/

I suspect the creating a hybrid sort path will help you with the ordering.

;with Menu as (
SELECT * FROM (
VALUES
('100','Main Menu','',1)
,('200','Sales Management','100',1)
,('300','Customer Relationship Management','200',1)
,('400','Setup','300',1)
,('500','Attribute','400',1)
,('515','Call Type','400',2)
,('504','Competitor','400',3)
,('410','General Operations','300',2)
,('521','Customer/Contact Import','410',1)
,('550','Mobile Connect Conflicts','410',2)
,('560','Mobile Connect Sync','410',3)
,('305','Help Desk','200',2)
) AS MenuTable(MenuID, MenuDesc,ParentMenuID, Seq)
)
, cte as (
SELECT MenuID,MenuDesc,ParentMenuID,Seq, 1 as Lev
, cast(cast(seq as varchar(10)) + ':' + menuid as varchar(MAX)) sortPathChar
from Menu
where ParentMenuID = ''
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenuID,m.Seq,Lev+1
, sortPathChar + '/' + cast(m.seq as varchar(10)) + ':' + m.menuid sortPathChar
from cte
join Menu m
on m.ParentMenuID=cte.MenuID
)
SELECT * FROM cte
ORDER BY sortPathChar

For your purpose I don't think you need the menuID in it, but i put it in for clarity

Fixed link
Post #1429010
Posted Sunday, March 10, 2013 6:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 12:05 PM
Points: 7, Visits: 8
mickyT, Thanks for your post. I see that your solution gives me what i need (not what i asked for, but perhaps better). How do a change it run against my menu table in the database? When i change your query to this:

with cte as (
SELECT MenuID,MenuDesc,ParentMenuID,Sequence, 1 as Lev
, cast(cast(Sequence as varchar(10)) + ':' + menuid as varchar(MAX)) sortPathChar
from Menu
where ParentMenuID = ''
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenuID,m.Sequence,Lev+1
, sortPathChar + '/' + cast(m.Sequence as varchar(10)) + ':' + m.menuid sortPathChar
from cte
join Menu m
on m.ParentMenuID=cte.MenuID
)
SELECT * FROM cte
ORDER BY sortPathChar

I get this error:

Lookup Error - SQL Server Database Error: Types don't match between the anchor and the recursive part in column "sortPathChar" of recursive query "cte".
Post #1429043
Posted Sunday, March 10, 2013 6:55 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:17 PM
Points: 1,095, Visits: 3,182
kwalter 22547 (3/10/2013)
mickyT, Thanks for your post. I see that your solution gives me what i need (not what i asked for, but perhaps better). How do a change it run against my menu table in the database? When i change your query to this:

with cte as (
SELECT MenuID,MenuDesc,ParentMenuID,Sequence, 1 as Lev
, cast(cast(Sequence as varchar(10)) + ':' + menuid as varchar(MAX)) sortPathChar
from Menu
where ParentMenuID = ''
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenuID,m.Sequence,Lev+1
, sortPathChar + '/' + cast(m.Sequence as varchar(10)) + ':' + m.menuid sortPathChar
from cte
join Menu m
on m.ParentMenuID=cte.MenuID
)
SELECT * FROM cte
ORDER BY sortPathChar

I get this error:

Lookup Error - SQL Server Database Error: Types don't match between the anchor and the recursive part in column "sortPathChar" of recursive query "cte".


Sorry I assumed that your MenuID was varchar since your ParentMenuID had an empty string. You will need to cast the menuid as varchar(10) on both sides of the recursive cte.

with cte as (
SELECT MenuID,MenuDesc,ParentMenuID,Sequence, 1 as Lev
, cast(cast(Sequence as varchar(10)) + ':' + cast(menuid as varchar(10)) as varchar(MAX)) sortPathChar
from Menu
where ParentMenuID = ''
UNION ALL
select m.MenuID,m.MenuDesc,m.ParentMenuID,m.Sequence,Lev+1
, sortPathChar + '/' + cast(m.Sequence as varchar(10)) + ':' + cast(m.menuid as varchar(10)) sortPathChar
from cte
join Menu m
on m.ParentMenuID=cte.MenuID
)
SELECT * FROM cte
ORDER BY sortPathChar

Post #1429048
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse