Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Recursively create Tree Structure Expand / Collapse
Author
Message
Posted Thursday, January 17, 2013 2:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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 

1 10 Test 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start vvvvv 0
3 10 Test_1 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start zfdf 1
4 10 Test_1_1 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start dfds 3
5 10 Test_1_2 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start sfd 4
7 10 Test_1_3 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start view 1
8 10 ZYX 2013-01-15 00:00:00.000 2013-01-23 00:00:00.000 Start View 10
2 10 Test1 2013-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start fdsf 0
6 10 Test1_1 2012-01-15 00:00:00.000 2013-01-16 00:00:00.000 Start sfd 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
Post #1408230
Posted Thursday, January 17, 2013 3:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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
Post #1408257
Posted Thursday, January 17, 2013 8:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:22 PM
Points: 3,636, Visits: 5,285
This is a self-help forum.

Help us to help you help yourself...



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1408697
Posted Thursday, January 17, 2013 9:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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
Post #1408700
Posted Thursday, January 17, 2013 9:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:22 PM
Points: 3,636, Visits: 5,285
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1408706
Posted Friday, January 18, 2013 4:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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.000 2013-01-04 00:00:00.000 Start 0
Union ALL
Select 2, 1, 'Administration', 2013-01-04 00:00:00.000 2013-01-07 00:00:00.000 Start 0
Union ALL
Select 3, 1, 'Information', 2013-01-07 00:00:00.000 2013-01-08 00:00:00.000 Start 0
Union ALL
Select 4, 1, 'Personal Information', 2013-01-10 00:00:00.000 2013-01-12 00:00:00.000 Start 0
Union ALL
Select 5, 1, 'School Setup', 2013-01-13 00:00:00.000 2013-01-15 00:00:00.000 Start 0
Union ALL
Select 6, 1, 'Sign Out', 2013-01-16 00:00:00.000 2013-01-17 00:00:00.000 Start 0
Union ALL
Select 7, 1, 'Welcome Page', 2013-01-18 00:00:00.000 2013-01-21 00:00:00.000 Start 0
Union ALL
Select 8, 1, 'Content Management', 2013-01-01 00:00:00.000 2013-01-03 00:00:00.000 Start 1
Union ALL
Select 9, 1, 'Content', 2013-01-01 00:00:00.000 2013-01-03 00:00:00.000 Start 8
Union ALL
Select 11, 1, 'Shared File List', 2013-01-08 00:00:00.000 2013-01-14 00:00:00.000 Start 8
Union ALL
Select 12, 1, 'Shared File List For Faculty', 2013-01-01 00:00:00.000 2013-01-07 00:00:00.000 Start 8
Union ALL
Select 13, 1, 'Alumni List', 2013-01-01 00:00:00.000 2013-01-05 00:00:00.000 Start 2
Union ALL
Select 15, 1, 'Content Management For Parent', 2013-01-01 00:00:00.000 2013-01-13 00:00:00.000 Start 1
Union ALL
Select 16, 1, 'InContent', 2013-01-15 00:00:00.000 2013-01-17 00:00:00.000 Start 9



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


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1408824
Posted Friday, January 18, 2013 4:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:22 PM
Points: 3,636, Visits: 5,285
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.000 2013-01-04 00:00:00.000 Start 0
Union ALL
Select 2, 1, 'Administration', 2013-01-04 00:00:00.000 2013-01-07 00:00:00.000 Start 0
Union ALL
Select 3, 1, 'Information', 2013-01-07 00:00:00.000 2013-01-08 00:00:00.000 Start 0
Union ALL
Select 4, 1, 'Personal Information', 2013-01-10 00:00:00.000 2013-01-12 00:00:00.000 Start 0
Union ALL
Select 5, 1, 'School Setup', 2013-01-13 00:00:00.000 2013-01-15 00:00:00.000 Start 0
Union ALL
Select 6, 1, 'Sign Out', 2013-01-16 00:00:00.000 2013-01-17 00:00:00.000 Start 0
Union ALL
Select 7, 1, 'Welcome Page', 2013-01-18 00:00:00.000 2013-01-21 00:00:00.000 Start 0
Union ALL
Select 8, 1, 'Content Management', 2013-01-01 00:00:00.000 2013-01-03 00:00:00.000 Start 1
Union ALL
Select 9, 1, 'Content', 2013-01-01 00:00:00.000 2013-01-03 00:00:00.000 Start 8
Union ALL
Select 11, 1, 'Shared File List', 2013-01-08 00:00:00.000 2013-01-14 00:00:00.000 Start 8
Union ALL
Select 12, 1, 'Shared File List For Faculty', 2013-01-01 00:00:00.000 2013-01-07 00:00:00.000 Start 8
Union ALL
Select 13, 1, 'Alumni List', 2013-01-01 00:00:00.000 2013-01-05 00:00:00.000 Start 2
Union ALL
Select 15, 1, 'Content Management For Parent', 2013-01-01 00:00:00.000 2013-01-13 00:00:00.000 Start 1
Union ALL
Select 16, 1, 'InContent', 2013-01-15 00:00:00.000 2013-01-17 00:00:00.000 Start 9



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


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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1408826
Posted Friday, January 18, 2013 5:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:52 PM
Points: 1,126, Visits: 1,590
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

1	1	Academic				        Start		0	0	1	1
8 1 Content Management Start 1 1 1 1/8
9 1 Content Start 8 2 1 1/8/9
11 1 Shared File List Start 8 2 1 1/8/11
12 1 Shared File List For Faculty Start 8 2 1 1/8/12
16 1 InContent Start 9 3 1 1/8/9/16
15 1 Content Management For Parent Start 1 1 1 1/15
2 1 Administration Start 0 0 2 2
13 1 Alumni List Start 2 1 2 2/13
3 1 Information Start 0 0 3 3
4 1 Personal Information Start 0 0 4 4
5 1 School Setup Start 0 0 5 5
6 1 Sign Out Start 0 0 6 6
7 1 Welcome Page Start 0 0 7 7

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
Post #1408831
Posted Friday, January 18, 2013 5:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:22 PM
Points: 3,636, Visits: 5,285
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1408845
Posted Friday, January 18, 2013 5:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 1,595, Visits: 6,620
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

Post #1408848
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse