#### Topic: To unite lists on the general elements

Greetings,  who directs to the necessary channel)
There is initial data
a, b
a, c
d, e
c, f
For every line it is necessary to return the list consisting of the general elements:

``````lst res
--- --------
a, b a, b, c, f &lt;- - because ' a ' it is connected with ' c ' (the second line), ' c ' it is connected with ' f ' (the last line)
a, c a, b, c, f &lt;- - it is similar, ' a ' it is connected with ' b ' (the first line), ' c ' it is connected with ' f ' (the last line)
d, e d, e &lt;- - neither ' d ', nor ' e ' to anybody are not connected
c, f a, b, c, f &lt;- - it is similar 1 and 2: ' c ' it is connected ' a ' (the second line), ' a ' it is connected with ' b ' (the first line).``````

In a reality kol-in lines it is measured one hundred thousand, kol-in elements in line from 1 to 300 (elements in line are unique).
Initial data -

``````with ds as
(
select ' a, b ' as lst from dual union all
select ' a, c ' from dual union all
select ' d, e ' from dual union all
select ' c, f ' from dual
)
select * from ds``````

#### Re: To unite lists on the general elements

Lexi-227;
[spoiler connected components: quick find quick union algorithm]

``````declare
type char_array is table of varchar2 (1) index by varchar2 (1);
type arr_elems is table of sys.ku \$ _ vcnt index by varchar2 (1);
root char_array;
root_elems arr_elems;
n varchar2 (1);
l integer: = dbms_utility.get_time ();
procedure print (v in varchar2) is
begin
dbms_output.put_line (to_char ((dbms_utility.get_time-l)/100, ' 0999.99 ') || ' ' || v);
l: = dbms_utility.get_time ();
end;
function get_root (n varchar2) return varchar2 is
begin
if root.exists (n) then
return root (n);
else
return null;
end if;
end;
procedure update_root (old_root varchar2, new_root varchar2) is
i pls_integer;
begin
if old_root! =new_root then
root_elems (new_root): =root_elems (new_root) multiset union all root_elems (old_root);
for i in 1. root_elems (old_root).count
loop
root (root_elems (old_root) (i)): =new_root;
end loop;
root_elems (old_root).delete;
end if;
end;
procedure add_elem (p_root varchar2, p_elem varchar2) is
begin
if not root_elems.exists (p_root) then
root_elems (p_root): =sys.ku \$ _ vcnt (p_elem);
else
root_elems (p_root).extend ();
root_elems (p_root) (root_elems (p_root).count): =p_elem;
end if;
end;
r1 varchar2 (1);
r2 varchar2 (1);
new_root varchar2 (1);
begin
r1: = get_root (p);
r2: = get_root (q);
if r1 is null or r2 is null then
new_root: = coalesce (r1, r2, p);
if r1 is null then add_elem (new_root, p); root (p): =new_root; end if;
if r2 is null then add_elem (new_root, q); root (q): =new_root; end if;
else
new_root: = least (r1, r2);
root (p): =new_root;
root (q): =new_root;
update_root (greatest (r1, r2), new_root);
end if;
end;
begin
print (' start ');
for r in (
==&gt; with ds as
(
==&gt; select ' a, b, x, y, z ' as lst from dual union all
==&gt; select ' a, c ' from dual union all
==&gt; select ' d, e ' from dual union all
==&gt; select ' c, f ' from dual
)
select substr (lst, 1, instr (lst, ', ')-1) p
,regexp_substr (lst, ' ([^,]) ', 1, n+1) q
from ds
,(select level n from dual connect by level &lt;=300) g
where g.n &lt;=regexp_count (lst, ' [^,] ')-1
)
loop
end loop;
print (' processed ');
print (' groups: ');
n: = root_elems.first ();
while n is not null loop
for i in 1. root_elems (n).count loop
dbms_output.put (', ' || root_elems (n) (i));
end loop;
dbms_output.put_line (' ');
n: = root_elems.next (n);
end loop;
end;``````

[/spoiler]
In the selected there should be your request

#### Re: To unite lists on the general elements

xtender;
Thanks big!
And on pure SQL, interesting, it probably to solve?

#### Re: To unite lists on the general elements

Lexi-227 wrote:

And on pure SQL, interesting, it probably to solve?

#### Re: To unite lists on the general elements

Lexi-227;
Here outlined on knees

``````with ds as
(
select ' a, b ' as lst from dual union all
select ' a, c ' from dual union all
select ' d, e ' from dual union all
select ' c, f ' lst from dual
)
select Root, listagg (Result _, ' / ') within group (order by Result _) Re
from (
select distinct connect_by_root lst Root, lst Result_
from (
select regexp_substr (lst, ' [^,] + ', 1, level) Symb
,lag (regexp_substr (lst, ' [^,] + ', 1, level), 1, substr (lst, instr (lst, ', ',-1) +1)) over (partition by lst order by level) Symb2
,lst
from ds
connect by level &lt;= length (regexp_replace (lst, ' [^,] + ')) +1
and prior lst = lst
and prior dbms_random.value IS NOT NULL
) T1
where Symb! = Symb2
connect by NOCYCLE Symb = prior Symb2 and lst! = prior lst
) T2
group by Root``````

Only laziness  Re for  values
Likely it is not optimal, but quickly it turned out ))

#### Re: To unite lists on the general elements

MaximaXXL;

Lexi-227 wrote:

In a reality kol-in lines it is measured one hundred thousand, kol-in elements in line from 1 to 300

and on such kol-ve?

#### Re: To unite lists on the general elements

xtender;
To 300 did not finish... 3 and 4 test silt, and here at 1 character - saw loss ((. Thanks.
Here corrected:

``````with ds as
(
select ' a, b ' as lst from dual union all
select ' a, c ' from dual union all
select ' d, e ' from dual union all
select ' c, f ' lst from dual
)
select Root, listagg (Result _, ' / ') within group (order by Result _) Re
from (
select distinct connect_by_root lst Root, lst Result_
from (
select regexp_substr (lst, ' [^,] + ', 1, level) Symb
,lag (regexp_substr (lst, ' [^,] + ', 1, level), 1, substr (lst, instr (lst, ', ',-1) +1)) over (partition by lst order by level) Symb2
,lst
from ds
connect by level &lt;= length (regexp_replace (lst, ' [^,] + ')) +1
and prior lst = lst
and prior dbms_random.value IS NOT NULL
) T1
--where Symb! = Symb2
connect by NOCYCLE Symb = prior Symb2 and lst! = prior lst
) T2
group by Root``````

#### Re: To unite lists on the general elements

Lexi-227;
The optimized version:
1) Selected case in new_root - an important piece for acceleration of operation with the big groups.
2) the Request transferred above that with logic not to hinder.
[spoiler]

``````declare
type char_array is table of varchar2 (1) index by varchar2 (1);
type arr_elems is table of sys.ku \$ _ vcnt index by varchar2 (1);
root char_array;
root_elems arr_elems;
n varchar2 (1);
cursor cur is
with ds as
(
select ' a, b, x, y, z ' as lst from dual union all
select ' a, c ' from dual union all
select ' d, e ' from dual union all
select ' c, f ' from dual
)
select substr (lst, 1, instr (lst, ', ')-1) p
,regexp_substr (lst, ' ([^,]) ', 1, n+1) q
from ds
,(select level n from dual connect by level &lt;=300) g
where g.n &lt;=regexp_count (lst, ' [^,] ')-1;
l integer: = dbms_utility.get_time ();
procedure print (v in varchar2) is
begin
dbms_output.put_line (to_char ((dbms_utility.get_time-l)/100, ' 0999.99 ') || ' ' || v);
l: = dbms_utility.get_time ();
end;
function get_root (n varchar2) return varchar2 is
begin
if root.exists (n) then
return root (n);
else
return null;
end if;
end;
procedure update_root (old_root varchar2, new_root varchar2) is
i pls_integer;
begin
if old_root! =new_root then
root_elems (new_root): =root_elems (new_root) multiset union all root_elems (old_root);
for i in 1. root_elems (old_root).count
loop
root (root_elems (old_root) (i)): =new_root;
end loop;
root_elems (old_root).delete;
end if;
end;
procedure add_elem (p_root varchar2, p_elem varchar2) is
begin
if not root_elems.exists (p_root) then
root_elems (p_root): =sys.ku \$ _ vcnt (p_elem);
else
root_elems (p_root).extend ();
root_elems (p_root) (root_elems (p_root).count): =p_elem;
end if;
end;
r1 varchar2 (1);
r2 varchar2 (1);
new_root varchar2 (1);
old_root varchar2 (1);
begin
r1: = get_root (p);
r2: = get_root (q);
if r1 is null or r2 is null then
new_root: = coalesce (r1, r2, p);
if r1 is null then add_elem (new_root, p); root (p): =new_root; end if;
if r2 is null then add_elem (new_root, q); root (q): =new_root; end if;
else
==&gt; case when root_elems (r1).count &lt;root_elems (r1).count
then new_root: = r1;
old_root: = r2;
else new_root: = r2;
old_root: = r1;
end case;
root (p): =new_root;
root (q): =new_root;
update_root (old_root, new_root);
end if;
end;
begin
print (' start ');
for r in cur
loop
end loop;
print (' processed ');
print (' groups: ');
n: = root_elems.first ();
while n is not null loop
for i in 1. root_elems (n).count loop
dbms_output.put (', ' || root_elems (n) (i));
end loop;
dbms_output.put_line (' ');
n: = root_elems.next (n);
end loop;
end;``````

[/spoiler]