[ORACLE] connect by 로 결과값 가로로 펼치기

column viewname format a100

with tab
as
(
select '0001' code, 'A' name from dual union all
select '0001' code, 'B' name from dual union all
select '0001' code, 'C' name from dual union all
select '0002' code, 'D' name from dual union all
select '0002' code, 'A' name from dual union all
select '0003' code, 'F' name from dual union all
select '0003' code, 'E' name from dual union all
select '0003' code, 'B' name from dual union all
select '0003' code, 'C' name from dual
)
select x.code
      ,substr(max(sys_connect_by_path(x.name,', ')),2) viewname
  from (
       select code
             ,name
             ,row_number() over (partition by code order by code ,name) rn
         from tab
       ) x
 start with x.rn = 1
connect by prior x.code = x.code
   and prior x.rn = x.rn - 1
 group by x.code
/

connect by 앞에서 항상 약해지는 나 ㅡ.ㅜ

with tab
as
(
select '0001' code, 'A' name from dual union all
select '0001' code, 'B' name from dual union all
select '0001' code, 'C' name from dual union all
select '0002' code, 'D' name from dual union all
select '0002' code, 'A' name from dual union all
select '0003' code, 'F' name from dual union all
select '0003' code, 'E' name from dual union all
select '0003' code, 'B' name from dual union all
select '0003' code, 'C' name from dual
)
select x.code
      ,substr(xmlagg(xmlelement(x,' ,',x.name).extract('//text()')),3) viewnames
  from tab x
 group by x.code
/

connect by 안쓰고 같은 효과내는 방법 xml 쿼리도 공부해야겠음 ㅋㅋ