SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help Deconstructing Tree Hierarchy Adjacency menu Table with T-SQL


Need Help Deconstructing Tree Hierarchy Adjacency menu Table with T-SQL

Author
Message
kwalter 22547
kwalter 22547
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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. w00t Any help or ideas would be extremely appreciated!

Thanks,
Keith
avinash.818
avinash.818
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 225
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
kwalter 22547
kwalter 22547
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
kwalter 22547
kwalter 22547
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
kwalter 22547
kwalter 22547
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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


kwalter 22547
kwalter 22547
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 8
Uh... Grasshopper, I just realized your ID is not "Grasshopper". sorry about that.
avinash.818
avinash.818
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 225
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.
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1594 Visits: 3317
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
kwalter 22547
kwalter 22547
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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".
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1594 Visits: 3317
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search