468,741 Members | 1,611 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,741 developers. It's quick & easy.

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
1 Week Ago #1
0 1324

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
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.