Recursively create Tree Structure

  • Hello Friends,

    Following is the DDL, Sample Data for the requirement I have :

    --DDL

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE 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]

    GO

    SET ANSI_PADDING OFF

    GO

    --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);

    Following is how I want the output to look like :

    --Desired Output

    110Test2013-01-15 00:00:00.0002013-01-16 00:00:00.000Startvvvvv0

    310Test_12013-01-15 00:00:00.0002013-01-16 00:00:00.000Startzfdf1

    410Test_1_12013-01-15 00:00:00.0002013-01-16 00:00:00.000Startdfds3

    510Test_1_22013-01-15 00:00:00.0002013-01-16 00:00:00.000Startsfd4

    710Test_1_32013-01-15 00:00:00.0002013-01-16 00:00:00.000Startview1

    810ZYX2013-01-15 00:00:00.0002013-01-23 00:00:00.000StartView10

    210Test12013-01-15 00:00:00.0002013-01-16 00:00:00.000Startfdsf0

    610Test1_12012-01-15 00:00:00.0002013-01-16 00:00:00.000Startsfd 2

    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.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Thanks Guys....

    I got it to work using the following Recursive query :

    ;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

    Got the solution from here after some research...thought it might be useful to someone. 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • This is a self-help forum. 😀

    Help us to help you help yourself...


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (1/17/2013)


    This is a self-help forum. 😀

    Help us to help you help yourself...

    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 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • I don't think there's a need to use ROW_NUMBER(), unless something like this doesn't get it for you:

    ;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 ModuleHieararchy

    ORDER BY SM1, SM2, n

    Ignoring your CONVERTs on date of course.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Hi Dwain,

    I don't think your query would work if I change the data a little.

    Try your query with this new data:

    Insert Into ES_SubModuleMaster

    Select 1,1,'Academic',2013-01-01 00:00:00.0002013-01-04 00:00:00.000Start0

    Union ALL

    Select 2,1,'Administration',2013-01-04 00:00:00.0002013-01-07 00:00:00.000Start0

    Union ALL

    Select 3,1,'Information',2013-01-07 00:00:00.0002013-01-08 00:00:00.000Start0

    Union ALL

    Select 4,1,'Personal Information',2013-01-10 00:00:00.0002013-01-12 00:00:00.000Start0

    Union ALL

    Select 5,1,'School Setup',2013-01-13 00:00:00.0002013-01-15 00:00:00.000Start0

    Union ALL

    Select 6,1,'Sign Out',2013-01-16 00:00:00.0002013-01-17 00:00:00.000Start0

    Union ALL

    Select 7,1,'Welcome Page',2013-01-18 00:00:00.0002013-01-21 00:00:00.000Start0

    Union ALL

    Select 8,1,'Content Management',2013-01-01 00:00:00.0002013-01-03 00:00:00.000Start1

    Union ALL

    Select 9,1,'Content',2013-01-01 00:00:00.0002013-01-03 00:00:00.000Start8

    Union ALL

    Select 11,1,'Shared File List',2013-01-08 00:00:00.0002013-01-14 00:00:00.000Start8

    Union ALL

    Select 12,1,'Shared File List For Faculty',2013-01-01 00:00:00.0002013-01-07 00:00:00.000Start8

    Union ALL

    Select 13,1,'Alumni List',2013-01-01 00:00:00.0002013-01-05 00:00:00.000Start2

    Union ALL

    Select 15,1,'Content Management For Parent',2013-01-01 00:00:00.0002013-01-13 00:00:00.000Start1

    Union ALL

    Select 16,1,'InContent',2013-01-15 00:00:00.0002013-01-17 00:00:00.000Start9

    Any other options??....I'm still stuck with the Ordering :crying:

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (1/18/2013)


    Hi Dwain,

    I don't think your query would work if I change the data a little.

    Try your query with this new data:

    Insert Into ES_SubModuleMaster

    Select 1,1,'Academic',2013-01-01 00:00:00.0002013-01-04 00:00:00.000Start0

    Union ALL

    Select 2,1,'Administration',2013-01-04 00:00:00.0002013-01-07 00:00:00.000Start0

    Union ALL

    Select 3,1,'Information',2013-01-07 00:00:00.0002013-01-08 00:00:00.000Start0

    Union ALL

    Select 4,1,'Personal Information',2013-01-10 00:00:00.0002013-01-12 00:00:00.000Start0

    Union ALL

    Select 5,1,'School Setup',2013-01-13 00:00:00.0002013-01-15 00:00:00.000Start0

    Union ALL

    Select 6,1,'Sign Out',2013-01-16 00:00:00.0002013-01-17 00:00:00.000Start0

    Union ALL

    Select 7,1,'Welcome Page',2013-01-18 00:00:00.0002013-01-21 00:00:00.000Start0

    Union ALL

    Select 8,1,'Content Management',2013-01-01 00:00:00.0002013-01-03 00:00:00.000Start1

    Union ALL

    Select 9,1,'Content',2013-01-01 00:00:00.0002013-01-03 00:00:00.000Start8

    Union ALL

    Select 11,1,'Shared File List',2013-01-08 00:00:00.0002013-01-14 00:00:00.000Start8

    Union ALL

    Select 12,1,'Shared File List For Faculty',2013-01-01 00:00:00.0002013-01-07 00:00:00.000Start8

    Union ALL

    Select 13,1,'Alumni List',2013-01-01 00:00:00.0002013-01-05 00:00:00.000Start2

    Union ALL

    Select 15,1,'Content Management For Parent',2013-01-01 00:00:00.0002013-01-13 00:00:00.000Start1

    Union ALL

    Select 16,1,'InContent',2013-01-15 00:00:00.0002013-01-17 00:00:00.000Start9

    Any other options??....I'm still stuck with the Ordering :crying:

    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.

    Edit: Oh yes. And if you'd like me to look again, please post expected results as I'd rather not guess. 🙂


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I modified your query a little....just to see the levels and the root columns and removed the dates as follows:

    ;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 ModuleHieararchy

    ORDER BY SM1, SM2, n

    11Academic Start0011

    81Content ManagementStart1111/8

    91Content Start8211/8/9

    111Shared File ListStart8211/8/11

    121Shared File List For Faculty Start8211/8/12

    161InContentStart9311/8/9/16

    151Content Management For Parent Start1111/15

    21AdministrationStart0022

    131Alumni ListStart2122/13

    31InformationStart0033

    41Personal Information Start0044

    51School SetupStart0055

    61Sign OutStart0066

    71Welcome PageStart0077

    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.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Not 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).

    ;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

    ,Tree

    FROM ModuleHieararchy

    ORDER BY SM1, SM2, SM3, n


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Just prefix the id's in the TreeOrder column with an appropriate number of zeros's.

    Test setup

    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 ALL

    Select 8,1,'Content Management',2,1,1,1,'1/8'

    Union ALL

    Select 15,1,'Content Management For Parent',3,1,1,1,'1/15'

    Union ALL

    Select 9,1,'Content',1,8,2,1,'1/8/9'

    Union ALL

    Select 11,1,'Shared File List',2,8,2,1,'1/8/11'

    Union ALL

    Select 12,1,'Shared File List For Faculty',3,8,2,1,'1/8/12'

    Union ALL

    Select 16,1,'InContent',1,9,3,1,'1/8/9/16'

    Union ALL

    Select 2,1,'Administration',2,0,0,2,'2'

    Union ALL

    Select 13,1,'Alumni List',10,2,1,2,'2/13'

    Union ALL

    Select 3,1,'Information',4,0,0,3,'3'

    Union ALL

    Select 4,1,'Personal Information',5,0,0,4,'4'

    Union ALL

    Select 5,1,'School Setup',1,0,0,5,'5'

    Union ALL

    Select 6,1,'Sign Out',8,0,0,6,'6'

    Union ALL

    Select 7,1,'Welcome Page',7,0,0,7,'7'

    select * from @table

    Solution

    ;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

    ModuleTree

    order by

    TreeOrder

  • Rats! Now why didn't I think of that?

    +1 Peter


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Thank 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. 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Hello 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 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply