WITH test AS
(
SELECT '홍길동' nam , '105' no , 'A' jong , '20091019' dat , '1' no1 , '2' no2 FROM dual UNION ALL
SELECT '홍길동' , '23' , 'B' , '20091019' , '3' , '4' FROM dual UNION ALL
SELECT '홍길동' , '148' , 'A' , '20091019' , '5' , '6' FROM dual UNION ALL
SELECT '홍길동' , '18' , 'B' , '20091019' , '7' , '8' FROM dual UNION ALL
SELECT '전우치' , '22' , 'B' , '20091019' , '9' , '10' FROM dual UNION ALL
SELECT '전우치' , '27' , 'A' , '20091019' , '11' , '12' FROM dual
)
SELECT nam ,
SubStr(cont,1,InStr(cont,',',1,1)-1) n_no1,
SubStr(cont,InStr(cont,',',1,1)+1,InStr(cont,',',1,2)-InStr(cont,',',1,1)-1) jong1,
SubStr(cont,InStr(cont,',',1,2)+1,InStr(cont,',',1,3)-InStr(cont,',',1,2)-1) dat1,
SubStr(cont,InStr(cont,',',1,3)+1,InStr(cont,',',1,4)-InStr(cont,',',1,3)-1) no1_1,
SubStr(cont,InStr(cont,',',1,4)+1,InStr(cont,',',1,5)-InStr(cont,',',1,4)-1) no2_1,
SubStr(cont,InStr(cont,',',1,5)+1,InStr(cont,',',1,6)-InStr(cont,',',1,5)-1) n_no2,
SubStr(cont,InStr(cont,',',1,6)+1,InStr(cont,',',1,7)-InStr(cont,',',1,6)-1) jong2,
SubStr(cont,InStr(cont,',',1,7)+1,InStr(cont,',',1,8)-InStr(cont,',',1,7)-1) dat2,
SubStr(cont,InStr(cont,',',1,8)+1,InStr(cont,',',1,9)-InStr(cont,',',1,8)-1) no1_2,
SubStr(cont,InStr(cont,',',1,9)+1,InStr(cont,',',1,10)-InStr(cont,',',1,9)-1) no2_2,
SubStr(cont,InStr(cont,',',1,10)+1,InStr(cont,',',1,11)-InStr(cont,',',1,10)-1) n_no3,
SubStr(cont,InStr(cont,',',1,11)+1,InStr(cont,',',1,12)-InStr(cont,',',1,11)-1) jong3,
SubStr(cont,InStr(cont,',',1,12)+1,InStr(cont,',',1,13)-InStr(cont,',',1,12)-1) dat3,
SubStr(cont,InStr(cont,',',1,13)+1,InStr(cont,',',1,14)-InStr(cont,',',1,13)-1) no1_3,
SubStr(cont,InStr(cont,',',1,14)+1,InStr(cont,',',1,15)-InStr(cont,',',1,14)-1) no2_3,
SubStr(cont,InStr(cont,',',1,15)+1,InStr(cont,',',1,16)-InStr(cont,',',1,15)-1) n_no4,
SubStr(cont,InStr(cont,',',1,16)+1,InStr(cont,',',1,17)-InStr(cont,',',1,16)-1) jong4,
SubStr(cont,InStr(cont,',',1,17)+1,InStr(cont,',',1,18)-InStr(cont,',',1,17)-1) dat4,
SubStr(cont,InStr(cont,',',1,18)+1,InStr(cont,',',1,19)-InStr(cont,',',1,18)-1) no1_4,
SubStr(cont||',',InStr(cont||',',',',1,19)+1,InStr(cont||',',',',1,20)-InStr(cont||',',',',1,19)-1) no2_4
FROM
(SELECT nam ,
REPLACE(SubStr(Max(sys_connect_by_path(no||'/'||jong||'/'||dat||'/'||no1||'/'||no2,',')),2),'/',',') cont
FROM
(SELECT nam ,
no ,
jong ,
dat ,
no1 ,
no2 ,
Row_Number() over(PARTITION BY nam ORDER BY no1) rn
FROM test)
START WITH rn = 1
CONNECT BY PRIOR rn = rn -1 AND PRIOR nam = nam
GROUP BY nam)

WITH test AS
(
SELECT '홍길동' nam , '105' no , 'A' jong , '20091019' dat , '1' no1 , '2' no2 FROM dual UNION ALL
SELECT '홍길동' , '23' , 'B' , '20091019' , '3' , '4' FROM dual UNION ALL
SELECT '홍길동' , '148' , 'A' , '20091019' , '5' , '6' FROM dual UNION ALL
SELECT '홍길동' , '18' , 'B' , '20091019' , '7' , '8' FROM dual UNION ALL
SELECT '전우치' , '22' , 'B' , '20091019' , '9' , '10' FROM dual UNION ALL
SELECT '전우치' , '27' , 'A' , '20091019' , '11' , '12' FROM dual
)
SELECT nam ,
Min(Decode(lv,1,cont)) n_no1 ,
Min(Decode(lv,2,cont)) jong1,
Min(Decode(lv,3,cont)) dat1,
Min(Decode(lv,4,cont)) no1_1,
Min(Decode(lv,5,cont)) no1_2,
Min(Decode(lv,6,cont)) n_no2,
Min(Decode(lv,7,cont)) jong2,
Min(Decode(lv,8,cont)) dat2,
Min(Decode(lv,9,cont)) no1_2,
Min(Decode(lv,10,cont)) no2_2,
Min(Decode(lv,11,cont)) n_no3,
Min(Decode(lv,12,cont)) jong3,
Min(Decode(lv,13,cont)) dat3,
Min(Decode(lv,14,cont)) no1_3,
Min(Decode(lv,15,cont)) no2_3,
Min(Decode(lv,16,cont)) n_no4,
Min(Decode(lv,17,cont)) jong4,
Min(Decode(lv,18,cont)) dat4,
Min(Decode(lv,19,cont)) no1_4,
Min(Decode(lv,20,cont)) no2_4
FROM
(SELECT nam ,
-- SubStr(cont,1,InStr(cont,',',1,lv)-1) n_no1,
SubStr(','||cont||',',InStr(','||cont||',',',',1,lv)+1,InStr(','||cont||',',',',1,lv+1)-InStr(','||cont||',',',',1,lv)-1) cont ,
lv
FROM
(SELECT nam ,
REPLACE(SubStr(Max(sys_connect_by_path(no||'/'||jong||'/'||dat||'/'||no1||'/'||no2,',')),2),'/',',') cont
FROM
(SELECT nam ,
no ,
jong ,
dat ,
no1 ,
no2 ,
Row_Number() over(PARTITION BY nam ORDER BY no1) rn
FROM test)
START WITH rn = 1
CONNECT BY PRIOR rn = rn -1 AND PRIOR nam = nam
GROUP BY nam) , (SELECT LEVEL lv FROM dual
CONNECT BY LEVEL <= 20)
)
GROUP BY nam


쿼리를 보시면 아시겠지만 해당 데이터가
더 늘어날수도 있기 때문에 어플리게이션이나
PL/SQL단에서 DYNAMIC SQL로 처리를 해야
능동적으로 조회 할 수 있습니다.