I have following table wherein 2 (can be more) foreign key columns define self-relationship between records of the table.

Hierarchical records in my problem dataset

I have seen many examples of using Recursive CTE in SQL when there is a single foreign key column (Parent ID) that defines hierarchy.

What modifications will be required to Recursive CTE when there are more than 1 (Parent columns) as in the image shown above ? An example of RCTE, if possible, would be useful.

I would eventually like these to be sorted by level and the expected end result is as attached below.

Expected End result

Thanks in advance !!

I have been able to achieve this using Programming language by making queries record-by-record for immediate children and then adjusting record level and running the queries again for 2nd Parent.

But my production dataset could be huge, a million+ records. Hence, I would like to do this using Recursive CTE if that’s the best way to solve this.

Programmatically, I had to resort to C# code to fetch the child records for parent records recursively as I admit that I am not a SQL expert. But it becomes very chatty and too many queries need to be fired for a larger dataset. I feel that perhaps Recursive CTE or some other native SQL way (Stored procedure or something) may be written to accomplish this.

Using a Recursive CTE with one column I was able to achieve the following result

With Children 
AS (
    SELECT 
        Id,
        Name,
        Parent1__c,
        Parent2__c,
        0 AS Level
    FROM CustomSelfRel__c
    WHERE Id = 'a00Hu000014KBJrIAO' AND NSPRemoved IS NULL

    UNION ALL

    SELECT
        T.Id,
        T.Name,
        T.Parent1__c,
        T.Parent2__c,
        Level + 1 AS Level
    FROM CustomSelfRel__c AS T
    JOIN Children  C ON C.Id = T.Parent1__c
) SELECT
    *
FROM
    Children;

Results using Recursive CTE that employs one Parent column

I have run this on Azure SQL DB using one Parent column Parent1__c. But I don’t know how to build this using two columns Parent1__c and Parent2__c.