By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tbl_nodes
(
nod1 varchar(50),
nod2 varchar(50),
nod_length float
);
insert into tbl_nodes values('A','B',1600);
insert into tbl_nodes values('G','H',45000);
insert into tbl_nodes values('B','C',2300);
insert into tbl_nodes values('C','D',2640);
insert into tbl_nodes values('B','D',2840);
insert into tbl_nodes values('C','E',2940);
insert into tbl_nodes values('D','F',2340);
insert into tbl_nodes values('M','N',9000);
insert into tbl_nodes values('E','A',100000);
9 rows affected
with cte AS (
select nod1, nod2, nod_length as Total_length,
convert(varchar(max), concat('-', nod1, '-', nod2, '-')) as nodes, 1 as lev
from tbl_nodes n
where nod1 = 'A'
union all
select cte.nod1, n.nod2, cte.Total_length + n.nod_length,
convert(varchar(max), concat(cte.nodes, n.nod2, '-')) as nodes, lev + 1
from cte join
tbl_nodes n
on cte.nod2 = n.nod1
where nodes not like concat('%-', n.nod2, '-%')
)
select nodes, total_length
from cte
where not exists (select 1
from cte cte2
where cte2.nodes like concat(cte.nodes, '_%'))
| nodes | total_length |
|---|---|
| -A-B-D-F- | 6780 |
| -A-B-C-E- | 6840 |
| -A-B-C-D-F- | 8880 |