Thanks...
WITH Resolver AS (
SELECT
[Level] = 1,
[Route] = CAST(RTRIM(t.ColA) AS VARCHAR(25)),
t.ColA, t.ColB, t.ColC, t.ColD, t.ColE, t.ColF
FROM tmpTable t
WHERE ColA = 395
UNION ALL
SELECT
[Level] = r.[Level] + 1,
[Route] = CAST(r.[Route] + '>' + CAST(RTRIM(t.ColA) AS VARCHAR(25)) AS VARCHAR(25)),
t.ColA, t.ColB, t.ColC, t.ColD, t.ColE, t.ColF
FROM Resolver r
INNER JOIN tmpTable t ON t.ColA = r.ColD
)
SELECT *
FROM Resolver
ORDER BY [Level], ColA;
-- three rows are returned: There is no row with value=0 in ColA.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden