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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Need Help Deconstructing Tree Hierarchy...

12 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

kwalter 22547

Posted Saturday, March 09, 2013 3:35 PM

Forum 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. w00t

Any help or ideas would be extremely appreciated!

Thanks,
Keith

Post #1428941

avinash.818

Posted Saturday, March 09, 2013 8:46 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 3:27 AM
Points: 24, Visits: 104

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

kwalter 22547

Posted Sunday, March 10, 2013 11:01 AM

Forum 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

kwalter 22547

Posted Sunday, March 10, 2013 12:04 PM

Forum 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

kwalter 22547

Posted Sunday, March 10, 2013 12:06 PM

Forum 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

kwalter 22547

Posted Sunday, March 10, 2013 12:22 PM

Forum 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

avinash.818

Posted Sunday, March 10, 2013 12:57 PM

Grasshopper

Group: General Forum Members
Last Login: Today @ 3:27 AM
Points: 24, Visits: 104

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

mickyT

Posted Sunday, March 10, 2013 1:08 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 297, Visits: 1,115

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

kwalter 22547

Posted Sunday, March 10, 2013 6:25 PM

Forum 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

mickyT

Posted Sunday, March 10, 2013 6:55 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:02 PM
Points: 297, Visits: 1,115

kwalter 22547 (3/10/2013)

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 »

12 posts, Page 1 of 2

1 2 »»

Permissions