10g에서 sys_connect_by_path 사용법

2011. 12. 1. 14:52Oracle/Oracle Study

반응형
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
;
반응형

'Oracle > Oracle Study' 카테고리의 다른 글

Datafile resize에 대한 오해(?)  (0) 2012.01.20
sh 계정 생성  (0) 2011.12.26
특정 IP만 접속  (0) 2011.11.21
Linux 10.2.0.4 upgrade Patch  (0) 2011.10.31
startup 종류?  (0) 2011.08.18