471,108 Members | 1,344 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Recursive CTE Permutations Help

3 2Bits
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
Jul 20 '21 #1
0 2825

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by SimonVC | last post: by
10 posts views Thread by Steve Goldman | last post: by
9 posts views Thread by JP SIngh | last post: by
2 posts views Thread by poofpoof | last post: by
3 posts views Thread by | last post: by
2 posts views Thread by | last post: by
3 posts views Thread by NatRoger | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.