I really want to share this cool use of sys_connect_by_path function!
To deal the unknown length of concatenating string, I need to concatenate multiple rows into 1 string without decaring at certain length such as varchar2(X).
So here is the magic, hope you like it...
select ltrim(sys_connect_by_path(names, ','),',') jonit_entities from (
select names, row_number() over (order by names) rn, count(*) over () cnt
from (select entity.pref_mail_name names
from prospect_entity P, entity
where P.Prospect_Id = &id
and P.ID_NUMBER = entity.id_number
order by P.PRIMARY_IND desc)
)
where rn = cnt
start with rn = 1
connect by prior rn = rn-1;
cheers, for detail information about sys_connect_by_path, please check here.
