InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 00:24:41 GMT20http://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspxYour welcomeMon, 11 Mar 2013 11:55:47 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspxmickyT, that's perfect. Thanks so much for your help!Mon, 11 Mar 2013 05:54:45 GMTkwalter 22547http://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspx[quote][b]kwalter 22547 (3/10/2013)[/b][hr]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:[code="sql"]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 cteORDER BY sortPathChar[/code]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".[/quote]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. [code="sql"]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 cteORDER BY sortPathChar[/code]Sun, 10 Mar 2013 18:55:57 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspxmickyT, 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:[code="sql"]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 cteORDER BY sortPathChar[/code]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".Sun, 10 Mar 2013 18:25:09 GMTkwalter 22547http://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspxHi Jeff Moden has this excellent article on hierarchies[url]http://www.sqlservercentral.com/articles/Hierarchy/94040/[/url]I suspect the creating a hybrid sort path will help you with the ordering.[code="sql"];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 cteORDER BY sortPathChar[/code]For your purpose I don't think you need the menuID in it, but i put it in for clarityFixed linkSun, 10 Mar 2013 13:08:48 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspxwith cte as(select MenuID,MenuDesc,ParentMenu,Sequence,1 as Levfrom Menuswhere ParentMenu is ''UNION ALLselect m.MenuID,m.MenuDesc,m.ParentMenu,m.Sequence,Lev+1from ctejoin Menus mon 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] ,Levfrom ctethis puts the correct sequencing,sorry for this, stii I didnt get enough clarity abut order column I see [b]Order[/b] 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,graphsmore information can be found in http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspxhope this helps.Sun, 10 Mar 2013 12:57:44 GMTavinash.818http://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspxUh... Grasshopper, I just realized your ID is not "Grasshopper". sorry about that.Sun, 10 Mar 2013 12:22:34 GMTkwalter 22547http://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspx[code="sql"]with cte as(select MenuID,MenuDesc,ParentMenuID,Sequence, 1 as Levfrom Menuwhere ParentMenuID = ''UNION ALLselect m.MenuID,m.MenuDesc,m.ParentMenuID,m.Sequence,Lev+1from ctejoin Menu mon m.ParentMenuID=cte.MenuID)select MenuID,MenuDesc,ParentMenuID,Sequence,ROW_NUMBER() over(order by Lev) as [Order],Levfrom cte[/code]Sun, 10 Mar 2013 12:06:08 GMTkwalter 22547http://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspxA 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: SubMenu2X+2th: SubSubMenu2etc. 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.-KeithSun, 10 Mar 2013 12:04:33 GMTkwalter 22547http://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspxGrasshopper: 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?[code="sql"]with cte as(select MenuID,MenuDesc,ParentMenuID,Sequence, 1 as Levfrom Menuwhere ParentMenuID is nullUNION ALLselect m.MenuID,m.MenuDesc,m.ParentMenuID,m.Sequence,Lev+1from ctejoin Menu mon m.ParentMenuID=cte.MenuID)select MenuID,MenuDesc,ParentMenuID,Sequence,ROW_NUMBER()over(order by MenuID) as [Order],Levfrom cte[/code]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,KeithSun, 10 Mar 2013 11:01:49 GMTkwalter 22547http://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspxHello,can you check using the below queryif the query is not okay,can you explain a bit about [b]Order[/b] Columnwith cte as(select MenuID,MenuDesc,ParentMenu,Sequence,1 as Levfrom Menuswhere ParentMenu is nullUNION ALLselect m.MenuID,m.MenuDesc,m.ParentMenu,m.Sequence,Lev+1from ctejoin Menus mon m.ParentMenu=cte.MenuID)select MenuID,MenuDesc,ParentMenu,Sequence,ROW_NUMBER()over(order by MenuID) as [Order] ,Levfrom cteSat, 09 Mar 2013 20:46:59 GMTavinash.818http://www.sqlservercentral.com/Forums/Topic1428941-392-1.aspxI have a Menu table with a structure like this:MenuID, MenuDesc, ParentMenuID, SequenceThe 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:[b]MenuID,MenuDesc,Parent Menu,Sequence100,Main Menu,[null],1200,Sales Management,100,1300,Customer Relationship Management,200,1400,Setup,300,1500,Attribute,400,1515,Call Type,400,2504,Competitor,400,3410,General Operations,300,2521,Customer/Contact Import,410,1550,Mobile Connect Conflicts,410,2560,Mobile Connect Sync,410,3305,Help Desk,200,2[/b]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:[b]MenuID,MenuDesc,Parent Menu,Sequence,Order,Level100,Main Menu,[null],1,1,1200,Sales Management,100,1,2,2300,Customer Relationship Management,200,1,3,3400,Setup,300,1,4,4500,Attribute,400,1,5,5515,Call Type,400,2,6,5504,Competitor,400,3,7,5410,General Operations,300,2,8,4521,Customer/Contact Import,410,1,9,5550,Mobile Connect Conflicts,410,2,10,5560,Mobile Connect Sync,410,3,11,5305,Help Desk,200,2,12,3[/b]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,KeithSat, 09 Mar 2013 15:35:25 GMTkwalter 22547