select id,max(sys_connect_by_path(name, ' ')) name from ( select id, name, c, min(c) over (partition by id) d from ( select id,name,row_number() over (order by id ,name)+ dense_rank() over (order by id) c from table_name)) start with c = d connect by c-1 = prior c group by id
from (
select id, name, c, min(c) over (partition by id) d
from (
select id,name,row_number() over (order by id ,name)+
dense_rank() over (order by id) c
from table_name))
start with c = d
connect by c-1 = prior c
group by id