개발은 너무해

10g에서 sys_connect_by_path 사용법 본문

Oracle/Oracle Study

10g에서 sys_connect_by_path 사용법

까칠 평생초보 2011.12.01 14:52
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
10g에서 sys_connect_by_path 사용법  (0) 2011.12.01
특정 IP만 접속  (0) 2011.11.21
Linux 10.2.0.4 upgrade Patch  (0) 2011.10.31
startup 종류?  (0) 2011.08.18
0 Comments
댓글쓰기 폼