Recursive self referenced table to flat structure

Question

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

Yes this is possible. If you want to learn how you need to provide some details. Like sample data and desired output. [mcve] — Sean Lange, Oct 16 '19 at 13:52
You have to use Common Table Expression https://stackoverflow.com/questions/4740748/when-to-use-common-table-expression-cte — Fábio Nascimento, Oct 16 '19 at 14:10
Pictures of data are nearly worthless. [Why](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557)? You need a recursive cte to solve this. — Sean Lange, Oct 16 '19 at 14:20

score 2 · Accepted Answer · answered Oct 16 '19 at 14:33

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

score 1 · Answer 2 · answered Oct 16 '19 at 14:41

The following should work for any number of levels:

DECLARE @t table
(
    ID char(1)
    , TypeAID char(1)
    , TypeBID char(1)
)

INSERT INTO @t (ID, TypeAID, TypeBID)
VALUES
('A', NULL, NULL)
, ('B', NULL, NULL)
, ('C', 'A', NULL)
, ('D', 'B', 'C')
, ('E', NULL, 'C')
;

WITH cte
AS
(
    SELECT
        Parent
        , ID Child
    FROM
        (
            SELECT
                ID
                , TypeAID
                , TypeBID
            FROM @t
        ) D
        UNPIVOT
        (
            Parent FOR TypeID IN (
                [TypeAID]
                , [TypeBID]
            )
        ) U
)
,

cte2
AS
(
    SELECT
        Parent
        , Child
    FROM cte

    UNION ALL

    SELECT
        cte1.Parent
        , cte2.Child
    FROM
        cte2 cte1
        JOIN cte cte2 ON cte1.Child = cte2.Parent
)

SELECT
    Parent
    , Child
FROM cte2
ORDER BY
    Parent
    , Child

Recursive self referenced table to flat structure

2 Answers2