SQL(ORACLE) 여러 행의 특정 컬럼값 -> 하나의 컬럼으로 합치기

2010.02.03 20:28DataBase/oracle

connect by prior / sys_connect_by_path

with t as (
    select 1 key1, '가' key2, '나' key3, 10 key4 from dual union all
    select 2 key1, '가' key2, '나' key3, 20 key4 from dual union all
    select 3 key1, '다' key2, '라' key3, 30 key4 from dual union all
    select 4 key1, '다' key2, '라' key3, 40 key4 from dual union all
    select 5 key1, '다' key2, '라' key3, 50 key4 from dual union all
    select 6 key1, '마' key2, '바' key3, 60 key4 from dual union all
    select 7 key1, '마' key2, '바' key3, 70 key4 from dual union all
    select 8 key1, '가' key2, '다' key3, 10 key4 from dual
)
select key2
     , key3
     , substr(max(sys_connect_by_path(key1||':'||key4, ',')), 2)
  from (select key1
             , key2
             , key3
             , key4
             , row_number() over(partition by key2, key3 order by key4) rn
          from t
        )
 start with rn = 1
 connect by prior key2 = key2 and prior rn = rn-1
 group by key2
        , key3