sql-serverrecursionself-referencing-table

Recursive self referenced table to flat structure


Entity
---------
ID
TypeAID
TypeBID

TypeAID and TypeBID are nullable fields where row can have one, both or none values entered, and both IDs are pointing to same table Entity.ID (self reference).

Is there any way to go recursive through 3 level deep structure and get flat structure of parent -> child, parent -> grandchild, child -> grandchild relations?

This is example where first table is normal hierarchy and second is desired flat output.

Data:

Row   ID    TypeAID    TypeBID
1     A     NULL       NULL
2     B     NULL       NULL
3     C     A          NULL
4     D     B          C
5     E     NULL       C

Desired result:

Row   Parent    Child
1     A         C
2     A         D
3     A         E
4     B         D
5     C         D
6     C         E

Solution

  • Using CTE:

    DECLARE @t TABLE (Id CHAR(1), TypeAId CHAR(1), TypeBId CHAR(1))
    
    INSERT INTO @t VALUES
        ('A', NULL, NULL),
        ('B', NULL, NULL),
        ('C', 'A', NULL),
        ('D', 'B', 'C'),
        ('E', NULL, 'C')
    
    -- All entities flattened
    ;WITH l1 AS (
        SELECT t.TypeAId AS Parent, t.Id AS Child
        FROM @t t
        WHERE t.TypeAId IS NOT NULL
        UNION
        SELECT t.TypeBId AS Parent, t.Id AS Child
        FROM @t t
        WHERE t.TypeBId IS NOT NULL)
    
    -- Join l1 with itself
    ,l2 AS (
        SELECT l1.Parent, l2.Child
        FROM l1 l1
        INNER JOIN l1 l2 ON l2.Parent = l1.Child)
    
    SELECT * FROM l1
    UNION ALL SELECT * FROM l2
    ORDER BY Parent