March 9, 2013 at 3:35 pm
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
March 9, 2013 at 8:47 pm
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
March 10, 2013 at 11:01 am
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
March 10, 2013 at 12:04 pm
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
March 10, 2013 at 12:06 pm
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
March 10, 2013 at 12:22 pm
Uh... Grasshopper, I just realized your ID is not "Grasshopper". sorry about that.
March 10, 2013 at 12:57 pm
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.
March 10, 2013 at 1:08 pm
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
March 10, 2013 at 6:25 pm
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".
March 10, 2013 at 6:55 pm
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
March 11, 2013 at 5:54 am
mickyT, that's perfect. Thanks so much for your help!
March 11, 2013 at 11:55 am
Your welcome
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply