InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 04:04:12 GMT20http://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxHello again guys.....how are you doing?The query is working perfectly fine. I have implemented the query and tested it for a tree having sub trees till the eighth level. Peter, i am still not very clear with the adding of the 0s to the TreeOrder string. Could you please tell me the logic behind doing so.....would love to know.Dwain, I'm really sorry for the missing commas in the Insert statements........My mind was completely occupied with the query even when I was posting the sample data.....but u still beat me to it :-)Mon, 21 Jan 2013 04:54:35 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxThank You very much Peter. :-) Your solution is working very well....Let me do some more testing and will get back if something's missing. Thank you Dwain for all your efforts. :-)Sat, 19 Jan 2013 00:12:03 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxRats! Now why didn't I think of that?+1 PeterFri, 18 Jan 2013 06:10:16 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxJust prefix the id's in the TreeOrder column with an appropriate number of zeros's.Test setup[code="sql"]Declare @table table(SModuleId Int, ModuleId Int, SMName Varchar(100), LinkOrder Int, ParentId int, Level int, Root int, TreeOrder Varchar(Max)) Insert Into @table Select 1, 1, 'Academic', 3, 0, 0, 1, '1'Union ALLSelect 8, 1, 'Content Management', 2, 1, 1, 1, '1/8'Union ALLSelect 15, 1, 'Content Management For Parent', 3, 1, 1, 1, '1/15'Union ALLSelect 9, 1, 'Content', 1, 8, 2, 1, '1/8/9'Union ALLSelect 11, 1, 'Shared File List', 2, 8, 2, 1, '1/8/11'Union ALLSelect 12, 1, 'Shared File List For Faculty', 3, 8, 2, 1, '1/8/12'Union ALLSelect 16, 1, 'InContent', 1, 9, 3, 1, '1/8/9/16'Union ALLSelect 2, 1, 'Administration', 2, 0, 0, 2, '2'Union ALLSelect 13, 1, 'Alumni List', 10, 2, 1, 2, '2/13'Union ALLSelect 3, 1, 'Information', 4, 0, 0, 3, '3'Union ALLSelect 4, 1, 'Personal Information', 5, 0, 0, 4, '4'Union ALLSelect 5, 1, 'School Setup', 1, 0, 0, 5, '5'Union ALLSelect 6, 1, 'Sign Out', 8, 0, 0, 6, '6'Union ALLSelect 7, 1, 'Welcome Page', 7, 0, 0, 7, '7'select * from @table[/code]Solution[code];with ModuleTree as( select t.SModuleId, t.ModuleId, t.SMName, t.SModuleId Root, 0 Level, right('00000000' + cast(SModuleId as varchar(max)), 9) TreeOrder from @table t where Parentid = 0 union all select t.SModuleId, t.ModuleId, t.SMName, t.Root, mt.Level + 1, mt.TreeOrder + '/' + right('000000000' + cast(t.SModuleId as varchar(4)), 9) from @table t join ModuleTree mt on mt.SModuleId = t.ParentId)select *from ModuleTreeorder by TreeOrder[/code]Fri, 18 Jan 2013 05:57:33 GMTPeter Brinkhaushttp://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxNot a fair race!You:- Removed the tool tip column from your INSERTs- Your INSERTs were missing tons of commas and quotes- You changed the IDENTITY_INSERT setting- I think you changed the name of the table too.- And most of all you tried to throw me off by forgetting to include IDs 10 and 14!!!!But nonetheless, as I said you needed to add an additional SMn for each level of depth you need to process. Hence my suggestion that you try a dynamic SQL solution (which I know you're good at so I won't presume to show you how).[code="sql"];WITH ModuleHieararchy AS ( SELECT [SModuleId], [ModuleId], [SMName] ,[SDate] ,[EDate] ,[Status] --,[ToolTip] ,[ParentId] ,n=1 ,SM1=[SModuleId] ,SM2=[SModuleId] ,SM3=[SModuleId] ,Tree=CAST([SModuleID] AS VARCHAR(8000)) FROM ES_SubModuleMaster1 WHERE [ParentId] = 0 UNION ALL SELECT a.[SModuleId], a.[ModuleId], a.[SMName] ,a.[SDate] ,a.[EDate] ,a.[Status] --,a.[ToolTip] ,a.[ParentId] ,n+1 ,SM1 ,SM2=CASE n WHEN 1 THEN a.[SModuleId] ELSE SM2 END ,SM3=CASE WHEN n >= 1 THEN a.[SModuleId] ELSE SM3 END ,Tree + '/' + CAST(a.[SModuleID] AS VARCHAR(8000)) FROM ES_SubModuleMaster1 a JOIN ModuleHieararchy b ON a.ParentID = b.sModuleID )SELECT [SModuleId], [ModuleId], [SMName] ,[SDate] ,[EDate] ,[Status] --,[ToolTip] ,[ParentId] ,n,sm1,sm2 ,TreeFROM ModuleHieararchyORDER BY SM1, SM2, SM3, n[/code]Fri, 18 Jan 2013 05:46:53 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxI modified your query a little....just to see the levels and the root columns and removed the dates as follows: [code="sql"];WITH ModuleHieararchy AS ( SELECT SModuleId, ModuleId, SMName ,Status ,ParentId ,0 As Level, SModuleId As Root, CAST(SModuleId AS varchar(MAX)) AS TreeOrder ,n=1 ,SM1=SModuleId ,SM2=SModuleId FROM ES_SubModuleMaster WHERE ParentId = 0 UNION ALL SELECT a.SModuleId, a.ModuleId, a.SMName ,a.Status ,a.ParentId ,b.Level + 1, b.Root As Root, b.TreeOrder+'/'+CAST(a.SmoduleId AS varchar(20)) AS TreeOrder ,n+1 ,SM1 ,SM2=a.SModuleId FROM ES_SubModuleMaster a JOIN ModuleHieararchy b ON a.ParentID = b.sModuleID )SELECT SModuleId, ModuleId, SMName ,Status ,ParentId ,Level,Root,TreeOrder FROM ModuleHieararchyORDER BY SM1, SM2, n[/code][code="sql"]1 1 Academic Start 0 0 1 18 1 Content Management Start 1 1 1 1/89 1 Content Start 8 2 1 1/8/911 1 Shared File List Start 8 2 1 1/8/1112 1 Shared File List For Faculty Start 8 2 1 1/8/1216 1 InContent Start 9 3 1 1/8/9/1615 1 Content Management For Parent Start 1 1 1 1/152 1 Administration Start 0 0 2 213 1 Alumni List Start 2 1 2 2/133 1 Information Start 0 0 3 34 1 Personal Information Start 0 0 4 45 1 School Setup Start 0 0 5 56 1 Sign Out Start 0 0 6 67 1 Welcome Page Start 0 0 7 7[/code]Following is the output that is required from the data that I posted in my last reply.....I hopw I can get it done before you for a change ;-)......thanks Dwain.Fri, 18 Jan 2013 05:06:16 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspx[quote][b]vinu512 (1/18/2013)[/b][hr]Hi Dwain, I don't think your query would work if I change the data a little. Try your query with this new data: [code="sql"]Insert Into ES_SubModuleMaster Select 1, 1, 'Academic', 2013-01-01 00:00:00.000 2013-01-04 00:00:00.000 Start 0Union ALLSelect 2, 1, 'Administration', 2013-01-04 00:00:00.000 2013-01-07 00:00:00.000 Start 0Union ALLSelect 3, 1, 'Information', 2013-01-07 00:00:00.000 2013-01-08 00:00:00.000 Start 0Union ALLSelect 4, 1, 'Personal Information', 2013-01-10 00:00:00.000 2013-01-12 00:00:00.000 Start 0Union ALLSelect 5, 1, 'School Setup', 2013-01-13 00:00:00.000 2013-01-15 00:00:00.000 Start 0Union ALLSelect 6, 1, 'Sign Out', 2013-01-16 00:00:00.000 2013-01-17 00:00:00.000 Start 0Union ALLSelect 7, 1, 'Welcome Page', 2013-01-18 00:00:00.000 2013-01-21 00:00:00.000 Start 0Union ALLSelect 8, 1, 'Content Management', 2013-01-01 00:00:00.000 2013-01-03 00:00:00.000 Start 1Union ALLSelect 9, 1, 'Content', 2013-01-01 00:00:00.000 2013-01-03 00:00:00.000 Start 8Union ALLSelect 11, 1, 'Shared File List', 2013-01-08 00:00:00.000 2013-01-14 00:00:00.000 Start 8Union ALLSelect 12, 1, 'Shared File List For Faculty', 2013-01-01 00:00:00.000 2013-01-07 00:00:00.000 Start 8Union ALLSelect 13, 1, 'Alumni List', 2013-01-01 00:00:00.000 2013-01-05 00:00:00.000 Start 2Union ALLSelect 15, 1, 'Content Management For Parent', 2013-01-01 00:00:00.000 2013-01-13 00:00:00.000 Start 1Union ALLSelect 16, 1, 'InContent', 2013-01-15 00:00:00.000 2013-01-17 00:00:00.000 Start 9[/code] Any other options??....I'm still stuck with the Ordering :crying:[/quote]It probably won't work if you added levels. See how I constructed SM1 and SM2? You'd need to do the same down to whatever level you think you've got and then use them in the ORDER BY.Didn't say it was a complete solution. Just all I had time for and there to get you thinking about other ways to get your ordering right. A fully generalized solution to any level might require dynamic SQL.[b]Edit[/b]: Oh yes. And if you'd like me to look again, please post expected results as I'd rather not guess. :-)Fri, 18 Jan 2013 04:46:15 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxHi Dwain, I don't think your query would work if I change the data a little. Try your query with this new data: [code="sql"]Insert Into ES_SubModuleMaster Select 1, 1, 'Academic', 2013-01-01 00:00:00.000 2013-01-04 00:00:00.000 Start 0Union ALLSelect 2, 1, 'Administration', 2013-01-04 00:00:00.000 2013-01-07 00:00:00.000 Start 0Union ALLSelect 3, 1, 'Information', 2013-01-07 00:00:00.000 2013-01-08 00:00:00.000 Start 0Union ALLSelect 4, 1, 'Personal Information', 2013-01-10 00:00:00.000 2013-01-12 00:00:00.000 Start 0Union ALLSelect 5, 1, 'School Setup', 2013-01-13 00:00:00.000 2013-01-15 00:00:00.000 Start 0Union ALLSelect 6, 1, 'Sign Out', 2013-01-16 00:00:00.000 2013-01-17 00:00:00.000 Start 0Union ALLSelect 7, 1, 'Welcome Page', 2013-01-18 00:00:00.000 2013-01-21 00:00:00.000 Start 0Union ALLSelect 8, 1, 'Content Management', 2013-01-01 00:00:00.000 2013-01-03 00:00:00.000 Start 1Union ALLSelect 9, 1, 'Content', 2013-01-01 00:00:00.000 2013-01-03 00:00:00.000 Start 8Union ALLSelect 11, 1, 'Shared File List', 2013-01-08 00:00:00.000 2013-01-14 00:00:00.000 Start 8Union ALLSelect 12, 1, 'Shared File List For Faculty', 2013-01-01 00:00:00.000 2013-01-07 00:00:00.000 Start 8Union ALLSelect 13, 1, 'Alumni List', 2013-01-01 00:00:00.000 2013-01-05 00:00:00.000 Start 2Union ALLSelect 15, 1, 'Content Management For Parent', 2013-01-01 00:00:00.000 2013-01-13 00:00:00.000 Start 1Union ALLSelect 16, 1, 'InContent', 2013-01-15 00:00:00.000 2013-01-17 00:00:00.000 Start 9[/code] Any other options??....I'm still stuck with the Ordering :crying:Fri, 18 Jan 2013 04:42:24 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxI don't think there's a need to use ROW_NUMBER(), unless something like this doesn't get it for you:[code="sql"];WITH ModuleHieararchy AS ( SELECT [SModuleId], [ModuleId], [SMName] ,[SDate] ,[EDate] ,[Status] ,[ToolTip] ,[ParentId] ,n=1 ,SM1=[SModuleId] ,SM2=[SModuleId] FROM ES_SubModuleMaster1 WHERE [ParentId] = 0 UNION ALL SELECT a.[SModuleId], a.[ModuleId], a.[SMName] ,a.[SDate] ,a.[EDate] ,a.[Status] ,a.[ToolTip] ,a.[ParentId] ,n+1 ,SM1 ,SM2=a.[SModuleId] FROM ES_SubModuleMaster1 a JOIN ModuleHieararchy b ON a.ParentID = b.sModuleID )SELECT [SModuleId], [ModuleId], [SMName] ,[SDate] ,[EDate] ,[Status] ,[ToolTip] ,[ParentId]FROM ModuleHieararchyORDER BY SM1, SM2, n[/code]Ignoring your CONVERTs on date of course.Thu, 17 Jan 2013 21:29:18 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspx[quote][b]dwain.c (1/17/2013)[/b][hr]This is a self-help forum. :-DHelp us to help you help yourself...[/quote]The above solution still wasn't complete....it wasn't getting the sub trees randomly and not in any particular order....I had to add rownumber to the above solution to get that done too....not very pretty but gets what I want....better options are still welcome..........would love to get updated on this Dwain :-)Thu, 17 Jan 2013 21:06:25 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxThis is a self-help forum. :-DHelp us to help you help yourself...Thu, 17 Jan 2013 20:19:32 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxThanks Guys....I got it to work using the following Recursive query : [code="sql"];With RCTE As ( Select SModuleId,ModuleId,SMName, Convert(char(11),SDate,106)as SDate, Convert(char(11), SDate, 103) as ddmmyyyySDate, Convert(char(11),EDate,106) as EDate, Convert(char(11), EDate, 103) as ddmmyyyyEDate, Status, ToolTip, ParentId, 0 As Level, SModuleId As Root, CAST(SModuleId AS varchar(MAX)) AS TreeOrder from ES_SubModuleMaster1 Where SModuleId=Coalesce(NULL,SModuleId) AND ParentId = 0 Union ALL Select b.SModuleId,b.ModuleId,b.SMName, Convert(char(11),b.SDate,106)as SDate, Convert(char(11), b.SDate, 103) as ddmmyyyySDate, Convert(char(11),b.EDate,106) as EDate, Convert(char(11), b.EDate, 103) as ddmmyyyyEDate, b.Status, b.ToolTip, b.ParentId, a.Level + 1, a.Root As Root, a.TreeOrder+'/'+CAST(b.SmoduleId AS varchar(20)) AS TreeOrder From RCTE As a JOIN ES_SubModuleMaster1 As b ON a.SModuleId = b.ParentId )Select * From RCTE Order By TreeOrder [/code]Got the solution from [url=http://stackoverflow.com/questions/10012961/recursive-sql-query-with-order-tree][b][u]here[/u][/b][/url] after some research...thought it might be useful to someone. :-)Thu, 17 Jan 2013 03:08:06 GMTvinu512http://www.sqlservercentral.com/Forums/Topic1408230-392-1.aspxHello Friends, Following is the DDL, Sample Data for the requirement I have : [code="sql"]--DDLSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[ES_SubModuleMaster1]( [SModuleId] [bigint] IDENTITY(1,1) NOT NULL, [ModuleId] [bigint] NOT NULL, [SMName] [varchar](100) NOT NULL, [SDate] [datetime] NULL, [EDate] [datetime] NULL, [Status] [varchar](10) NOT NULL, [ToolTip] [varchar](500) NULL, [ParentId] [bigint] NOT NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGO --Sample Data Insert Into ES_SubModuleMaster1 Values(10, 'Test', '2013-01-15 00:00:00.000', '2013-01-16 00:00:00.000', 'Start', 'vvvvv', 0);Insert Into ES_SubModuleMaster1 Values(10, 'Test1', '2013-01-15 00:00:00.000', '2013-01-16 00:00:00.000', 'Start', 'fdsf', 0);Insert Into ES_SubModuleMaster1 Values(10, 'Test_1', '2013-01-15 00:00:00.000', '2013-01-16 00:00:00.000', 'Start', 'zfdf', 1);Insert Into ES_SubModuleMaster1 Values(10, 'Test_1_1', '2013-01-15 00:00:00.000', '2013-01-16 00:00:00.000', 'Start', 'dfds', 3);Insert Into ES_SubModuleMaster1 Values(10, 'Test_1_2', '2013-01-15 00:00:00.000', '2013-01-16 00:00:00.000', 'Start', 'sfd', 4);Insert Into ES_SubModuleMaster1 Values(10, 'Test1_1', '2012-01-15 00:00:00.000', '2013-01-16 00:00:00.000', 'Start', 'sfd', 2);Insert Into ES_SubModuleMaster1 Values(10, 'Test_1_3', '2013-01-15 00:00:00.000', '2013-01-16 00:00:00.000', 'Start', 'view', 1);Insert Into ES_SubModuleMaster1 Values(10, 'ZYX', '2013-01-15 00:00:00.000', '2013-01-23 00:00:00.000', 'Start', 'View', 1);[/code]Following is how I want the output to look like : [code="sql"]--Desired Output 1 10 Test 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start vvvvv 03 10 Test_1 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start zfdf 14 10 Test_1_1 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start dfds 35 10 Test_1_2 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start sfd 47 10 Test_1_3 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start view 18 10 ZYX 2013-01-15 00:00:00.000 2013-01-23 00:00:00.000 Start View 102 10 Test1 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start fdsf 06 10 Test1_1 2012-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start sfd 2[/code] I want to display a Tree like structure where the first node starts from the row where ParentId = 0, then get the first sub node ie: the row where ParentId = 1.....the first sub node would be the row with SModuleId 3....then get all the sub nodes where ParentId would be 3.Do the same for all sub nodes, before moving on to the next node where ParentId = 0 ie: the row where SModuleId = 2 and similarly get all the sub nodes and sub nodes of these subnodes.........just like a complete tree. I hope the above explanation was helpful.Can this be done without using a Cursor?....All suggestions are very very welcome....Looking forward to your replies.Thu, 17 Jan 2013 02:14:11 GMTvinu512