/********************************************************************************************
-- Title : [ORAORA10g] 다중로우를 단일로우로 변환하는 함수
-- Reference : http://dbrang.tistory.com/667
-- Key word : 싱글값 멀티값 xmlagg xmlelement SYS_CONNECT_BY_PATH pivot crosstab
********************************************************************************************/
-- 테이블 생성
-- drop table scott.ttt purge;
create table scott.ttt
( aa nvarchar2(10) not null
, bb nvarchar2(10) not null
)
-- 데이터 입력
insert into scott.ttt
select 'A0001', 'A1A1' from dual union all
select 'A0001', 'A2A2' from dual union all
select 'A0001', 'A3A3' from dual union all
select 'B0001', 'B1B1' from dual union all
select 'B0001', 'B2B2' from dual union all
select 'C0001', 'C1C1' from dual union all
select 'D0001', 'D1D1' from dual union all
select 'D0001', 'D2D2' from dual union all
select 'D0001', 'D3D3' from dual union all
select 'D0001', 'D4D4' from dual union all
select 'D0001', 'D5D5' from dual union all
select 'E0001', 'E4E4' from dual;
commit;
-- 확인
select * from scott.ttt;
-- 함수 생성
create or replace function scott.fn_ttt
( v_AA in nvarchar2 )
return nvarchar2
is
i_ret nvarchar2(4000);
begin
SELECT --A.AA,
MAX(SUBSTR(SYS_CONNECT_BY_PATH(A.BB, '|'), 2)) AS BB
into i_ret
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY AA
ORDER BY AA
) AS RNUM
, AA, BB
FROM scott.ttt
WHERE AA = v_AA
) A
START WITH A.RNUM = 1
CONNECT BY PRIOR A.RNUM = A.RNUM - 1 AND PRIOR A.AA = A.AA
GROUP BY A.AA;
return(i_ret);
exception
when no_data_found then return ('no_data_found');
when too_many_rows then return ('too_many_rows');
when others then raise;
end fn_ttt;
-- 확인
select scott.fn_ttt('A0001') from dual union all
select scott.fn_ttt('B0001') from dual union all
select scott.fn_ttt('C0001') from dual union all
select scott.fn_ttt('D0001') from dual union all
select scott.fn_ttt('E0001') from dual;