Recursively create Tree Structure

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

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

;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

;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

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

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

;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

;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

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

;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