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 쿼리도 공부해야겠음 ㅋㅋ
