I have the following example, which is reaching the max recursion.
Declare @example table (RowID varchar(20), RelID varchar(20))
Insert Into @example Select 'Rec1', 'Rec5'
Insert Into @example Select 'Rec1', 'Rec6'
Insert Into @example Select 'Rec5', 'Rec1'
Insert Into @example Select 'Rec5', 'Rec2'
Insert Into @example Select 'Rec6', 'Rec1'
Insert Into @example Select 'Rec6', 'Rec3'
Insert Into @example Select 'Rec6', 'Rec4'
Insert Into @example Select 'Rec6', 'Rec5'
;With RelatedCTE (RowID, RelID, ComboID) as
(
Select RowID, RelID, RowID + '-' + RelID
From @example
UNION ALL
Select r1.RowID, r2.RelID, r1.RowID + '-' + r2.RelID
From RelatedCTE r1
INNER JOIN @example r2 ON r1.RelID = r2.RowID
Where r1.ComboID <> r1.RowID + '-' + r2.RelID
and r1.RowID <> r2.RowID
--and r2.RelID <> r1.RelID
)
Select *
From RelatedCTE
What I'm trying to get out of the output, is a unique list that looks like this:
Rec1-Rec5
Rec1-Rec6
Rec5-Rec1
Rec5-Rec2
Rec6-Rec1
Rec6-Rec3
Rec6-Rec4
Rec6-Rec5
Rec1-Rec2
Rec1-Rec3
Rec1-Rec4
Rec5-Rec3
Rec5-Rec4
Rec5-Rec6
Rec6-Rec2
Rec2-Rec1
Rec2-Rec3
Rec2-Rec4
Rec2-Rec5
Rec2-Rec6
Rec3-Rec1
Rec3-Rec2
Rec3-Rec4
Rec3-Rec5
Rec3-Rec6
Rec4-Rec1
Rec4-Rec2
Rec4-Rec3
Rec4-Rec5
Rec4-Rec6