By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE nodes (
name INT,
next INT,
result INT
)
INSERT INTO nodes VALUES
(1000, 1100, 1),
(1100, 1200, 2),
(1100, 2000, 2),
(1200, 1300, 3),
(2000, 3000, 3),
(3000, 4000, 4),
(1300, 1400, 4),
(1400, 1500, 5),
(4000, 5000, 5),
(4000, 4100, 5)
10 rows affected
WITH
leaf AS
(
SELECT *
FROM nodes
WHERE NOT EXISTS (SELECT *
FROM nodes AS lookup
WHERE lookup.name = nodes.next
)
),
recursed_path AS
(
SELECT
*,
next AS branch_name,
0 AS level
FROM
leaf
UNION ALL
SELECT
root.*,
leaf.branch_name,
level + 1
FROM
recursed_path AS leaf
INNER JOIN
nodes AS root
ON root.next = leaf.name
)
SELECT
*
FROM
recursed_path
ORDER BY
| name | next | result | branch_name | level |
|---|---|---|---|---|
| 1000 | 1100 | 1 | 1500 | 4 |
| 1100 | 1200 | 2 | 1500 | 3 |
| 1200 | 1300 | 3 | 1500 | 2 |
| 1300 | 1400 | 4 | 1500 | 1 |
| 1400 | 1500 | 5 | 1500 | 0 |
| 1000 | 1100 | 1 | 4100 | 4 |
| 1100 | 2000 | 2 | 4100 | 3 |
| 2000 | 3000 | 3 | 4100 | 2 |
| 3000 | 4000 | 4 | 4100 | 1 |
| 4000 | 4100 | 5 | 4100 | 0 |
| 1000 | 1100 | 1 | 5000 | 4 |
| 1100 | 2000 | 2 | 5000 | 3 |
| 2000 | 3000 | 3 | 5000 | 2 |
| 3000 | 4000 | 4 | 5000 | 1 |
| 4000 | 5000 | 5 | 5000 | 0 |