Get root and all successors from parent child table on SQL Server-CodePudding

I'm using ms sql server. I have a simple table with a parent child relationship, like Parent, Child

Now I need a query which delivers every Element with itself, all its successors and successors of them an so on. Example

Parent Child
------ -----
A      A1
A1     A11
A      A2
A2     A21
A21    A211

Now the result should look like:

Root   Successor
------ ---------
A      A
A      A1
A      A11
A      A2
A      A21
A      A211
A1     A1
A1     A11
A11    A11
A2     A2
A2     A21
A2     A211
A21    A21
A21    A211
A211   A211

Any idea to do it recursivly on MS SQL Server with a sql query?

I searched for the internet and found some solutions, but not for my problem. I never got a list with the root and all its successor, only parent child for one level.

CodePudding user response：

WITH CTE_AllRoots AS 
(
    SELECT Parent as Root FROM Table1
    UNION 
    SELECT Child FROM Table1
)
, RCTE AS 
(
    SELECT Root, Root AS Successor FROM CTE_AllRoots
    UNION ALL
    SELECT r.Root, t.Child
    FROM RCTE r
    INNER JOIN Table1 t ON t.Parent = r.Successor
)
SELECT * 
FROM RCTE
ORDER BY Root, Successor

db<>fiddle

CodePudding user response：

We could also use CROSS JOIN:

SELECT
y1.parent AS Root, y2.child AS Successor
FROM 
yourtable AS y1 
CROSS JOIN 
yourtable AS y2
WHERE y2.child LIKE y1.parent   '%'
UNION
SELECT parent, parent FROM yourtable
UNION
SELECT child, child FROM yourtable
ORDER BY Root, Successor;

Note: This could get slow in case your table has a very high amount of rows due to the LIKE condition in the WHERE clause.

I think using the CTE approach should be prefered here, I just wanted to show you another option.

db<>fiddle