Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have the following table
FROM | TO
---------
P1 | P2
P2 | P3
P3 | P4
P4 | NULL
P5 | P3
P6 | P7
P7 | P6
P1 gives to P2 but as P2 gives to P3, P1 indirectly gives to P3 but has P3 gives to P4, P1 indirectly gives to P4 as P4 gives to nobody it is the final destination.

I have to take care of circular reference e.g. P6 gives to P7 but P7 gives to P6 so avoid infinite loop.

Based on the previous table I want to create the final table wich will be
P1 | P4
P2 | P4
P3 | P4
P4 | NULL
P5 | P4
P6 | P7
P7 | P6

what is the best way to do achieve CTE Recursive or a loop in a Function with cursor?

What I have tried:

I have done the following after that works for the first step, but I don't know not how to make it recursive to get the final result

SQL
;WITH CTE AS (
select m1.FROM, m2.TO 
from Table m2 join Table m1 
on m2.FROM=m1.TO and m2.TO is not null and m2.TO<>m1.From
)
update TmpTable set TO = CTE.TO from CTE where Table.FROM = CTE.FROM
Posted
Updated 30-Apr-20 5:59am
Comments
CHill60 30-Apr-20 11:19am    
Definitely avoid a cursor! "Best" is not a word to associate with cursors … usually "last resort" :-)
I'll have a proper look when I free up (I'm at work) but this looks similar to a hierarchy problem that I used in my article Processing Loops in SQL Server[^] - that example doesn't handle your infinite loop condition though

1 solution

There might be a cleaner way to do this, but this seems to work:
SQL
WITH cteRawData As
(
    SELECT
        [From],
        [To],
        '/' + CAST([From] As varchar(max)) + '/' + CAST([To] As varchar(max)) + '/' As [Path],
        1 As Depth
    FROM
        [Table]
    
    UNION ALL
    
    SELECT
        F.[From],
        T.[To],
        F.[Path] + CAST(T.[To] As varchar(max)) + '/',
        F.Depth + 1
    FROM
        cteRawData As F
        INNER JOIN [Table] As T
        ON T.[From] = F.[To]
    WHERE
        F.[Path] Not Like '%/' + CAST(T.[To] As varchar(max)) + '/%'
),
cteData As
(
    SELECT
        [From],
        [To],
        ROW_NUMBER() OVER (PARTITION BY [From] ORDER BY Depth DESC) As RN
    FROM
        cteRawData
)
SELECT
    [From],
    [To]
FROM
    cteData
WHERE
    RN = 1
ORDER BY
    [From]
;
 
Share this answer
 
Comments
Maciej Los 30-Apr-20 14:53pm    
Oh, no! This is beautiful!
CHill60 1-May-20 5:55am    
Came back to answer this question and found .. this!
5'd
Bed ouin 1-May-20 6:24am    
Fantastic, works like a charm.
Brilliant.
Thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900