Recursive CTE Permutations Help

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Canes816
    New Member
    • Nov 2006
    • 3

    Recursive CTE Permutations Help

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