CREATE TABLE test (SeqNo INT, StartStep CHAR(1), EndStep CHAR(1));
INSERT INTO test VALUES
(788, 'A', 'B'),
(788, 'A', 'C'),
(795, 'B', 'C'),
(797, 'C', 'D'),
(798, 'D', 'F'),
(798, 'D', 'G');
SELECT * FROM test;
WITH
cte1 AS (
SELECT *,
DENSE_RANK() OVER (ORDER BY SeqNo) dr,
ROW_NUMBER() OVER (ORDER BY SeqNo) rn
FROM test
),
cte2 AS (
SELECT SeqNo, StartStep, EndStep, CAST(',' + StartStep + ',' + EndStep + ',' AS NVARCHAR) path, rn, 1 sn
FROM cte1
WHERE dr = 1
UNION ALL
SELECT cte1.SeqNo, cte1.StartStep, cte1.EndStep, CAST(cte2.path + cte1.EndStep + ',' AS NVARCHAR), cte2.rn, cte2.sn + 1
FROM cte2
JOIN cte1 ON cte2.EndStep = cte1.StartStep
),
cte3 AS (
SELECT rn, MAX(sn) sn
FROM cte2
GROUP BY rn
)
SELECT test.SeqNo, test.StartStep, test.EndStep
FROM cte2
JOIN cte3 ON cte2.rn = cte3.rn
AND cte2.sn = cte3.sn
JOIN test ON CHARINDEX(',' + test.StartStep + ',' + test.EndStep + ',', cte2.path) > 0
ORDER BY cte2.path, test.SeqNo
;
Telegram: DB developers channel