10g에서 sys_connect_by_path 사용법

create table temp_nation as

select 'Seoul' city, 'Korea' country from dual union all

select 'Busan' city, 'Korea' country from dual union all

select 'Inchon' city, 'Korea' country from dual union all

select 'LA' city, 'America' country from dual union all

select 'Newyork' city, 'America' country from dual union all
select 'Mexicocity' city, 'Mexico' country from dual

;

select country, substr(max(sys_connect_by_path(city,',')),2) result

from

(

    select city, country, row_number() over (partition by country order by city) rnum

    from   temp_nation

)

start with rnum=1

connect by prior rnum=rnum-1

and prior country=country    --> group별로 지정할 경우

group by country

;