I have a dataset with network nodes and which one is referred to be another network node)
K1 = From
K2 = To
I need to produce one row for each group (ie all interconnected nodes)
When I run with the below data the result is correct:
A A,B,C,H,M,P
D D,E,K
F F,G
J J
But when I add another testdata:
insert into jlttest values ('F','M');
I do not get the expect result which should be like this since the group with F and G is now connected to the first group through M.
A A,B,C,H,M,P, F, G
D D,E,K
J J
So I know that my algorithm and code below using recursivw WITH is not correct, but can anyone help me out here:
drop table jlttest;
create table jlttest(k1 varchar2(3),k2 varchar2(3));
insert into jlttest values ('A','B');
insert into jlttest values ('B','A');
insert into jlttest values ('A','H');
insert into jlttest values ('B','C');
insert into jlttest values ('H','M');
insert into jlttest values ('J',null);
insert into jlttest values ('M','P');
insert into jlttest values ('D','E');
insert into jlttest values ('F','G');
insert into jlttest values ('E','K');
-----insert into jlttest values ('F','M');
commit
/
---- kunn også laves med et view, men lige nu for nemheds skyld sådan her !
update jlttest set k1 = k2, k2 = k1
where k2 < k1;
commit;
with netv (rod, k1, k2) as
(select k1 rod, root.k1, root.k2
from jlttest root
where root.k1 not in (select k2 from jlttest where k2 is not null)
union all
select parent.rod, child.k1, child.k2
from netv parent, jlttest child
where parent.k2 = child.k1)
----------
select rod, listagg(k1,',') WITHIN GROUP (ORDER BY k1 ) destinationer
from
(
select distinct rod, k1 from netv union
select distinct rod, k2 from netv
)
group by rod
order by rod
;
Regards
Mette