SQLTeam.com | Weblogs | Forums

Recursive CTE


#1

I want to add Maximum Level # on the basis of those activity that has more than one Parent and have LNK ='S' only.

Desired Result:
Row# Child_Task_Name Parent_Task_Name Lnk Lnk_Type Level (Add up previous level value with those activities having more than one parent and LNK value = S )

1	T1		S	SS	1
2	T1	T4	S	SS	2
3	T1	T5	S	SS	3
4	T1	T6	S	SS	4
5	T2	T1	P	SS	1
6	T2	T3	S	FS	1
7	T3	T2	P	FS	2
8	T3	T4	S	FF	1
9	T4	T1	P	SS	1
10	T4	T3	P	FF	2
11	T4	T5	S	SS	1
12	T5	T1	P	SS	1
13	T5	T4	P	SS	1
14	T5	T6	S	FS	2
15	T6	T1	P	SS	1
16	T6	T5	P	FS	1

#2

Hi

Please give

  1. Create Data Script

Thanks


#3

Create Table #Relations
(Row# Integer,
Child_Task_Name VarChar(20),
Parent_Task_Name VarChar(20),
Lnk VarChar(1),
Lnk_Type VarChar(2),
LevelID Integer)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (1, 'T1','','S','SS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (2, 'T1','T4','S','SS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (3, 'T1','T5','S','SS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (4, 'T1','T6','S','SS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (5, 'T2','T1','P','SS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (6, 'T2','T3','S','FS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (7, 'T3','T2','P','FS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (8, 'T3','T4','S','FF',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (9, 'T4','T1','P','SS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (10, 'T4','T3','P','FF',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (11, 'T4','T5','S','SS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (12, 'T5','T1','P','SS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (13, 'T5','T4','P','SS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (14, 'T5','T6','S','FS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (15, 'T6','T1','P','SS',0)

Insert Into #Relations
( Row# , Child_Task_Name, Parent_Task_Name, Lnk , Lnk_Type, LevelID )
Values (16, 'T6','T5','P','FS',0)