Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursively create Tree Structure


Recursively create Tree Structure

Author
Message
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
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 ;-)
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
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
      Wink
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 ;-)
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
This is a self-help forum. :-D

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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
dwain.c (1/17/2013)
This is a self-help forum. :-D

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 ;-)
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
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 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 ;-)
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
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 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!

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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
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 ;-)
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Peter Brinkhaus
Peter Brinkhaus
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1676 Visits: 7064
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search