WITH a_table AS
(
SELECT '01' code, '김' nm, '001' sid, '서울' addr FROM dual
UNION ALL SELECT '02', '이', '002', '부산' FROM dual
UNION ALL SELECT '03', '박', '003', '인천' FROM dual
)
, b_table AS
(
SELECT '01' code, '국어' test, '작문' ynm, '1997' tdate, 95 tscore FROM dual
UNION ALL SELECT '01', '국어', '논술', '1997', 80 FROM dual
UNION ALL SELECT '01', '영어', 'TIC' , '2000', 35 FROM dual
UNION ALL SELECT '01', '영어', 'ACC' , '2004', 44 FROM dual
UNION ALL SELECT '02', '국어', '논술', '1995', 20 FROM dual
UNION ALL SELECT '02', '국어', '발음', '1999', 30 FROM dual
UNION ALL SELECT '02', '영어', 'TES ', '2003', 85 FROM dual
UNION ALL SELECT '02', '영어', 'KEB ', '2011', 49 FROM dual
)
SELECT a.code, a.nm, a.sid, a.addr
     , b.국어, b.영어
  FROM a_table a
     , (SELECT code, test
             , LISTAGG(ynm||' '||tdate||' '||tscore, ' | ')
               WITHIN GROUP(ORDER BY tdate) v
          FROM b_table
         GROUP BY code, test
       ) PIVOT (MIN(v) FOR test IN ('국어' 국어, '영어' 영어)) b
 WHERE a.code = b.code
 ORDER BY a.code
;

감사합니다.

그런데

LISTAGG(ynm||' '||tdate||' '||tscore, ' | ')

WITHIN GROUP(ORDER BY tdate) v

요 부분은 11g이상에서 지원하는 함수여서

현재 9i 버전이다 보니 사용할 수가 없네요 .. ㅠㅠ

도움 주셨는데 죄송합니다.....

SELECT a.code, a.nm, a.sid, a.addr
     , b.국어, b.영어
  FROM a_table a
     , (SELECT code
             , MIN(DECODE(test, '국어', v)) 국어
             , MIN(DECODE(test, '영어', v)) 영어
          FROM (SELECT code, test
                     , SUBSTR(XMLAGG(
                       XMLELEMENT(x, ' | ', ynm||' '||tdate||' '||tscore)
                       ORDER BY tdate).EXTRACT('//text()'), 4) v
                  FROM b_table
                 GROUP BY code, test
               )
         GROUP BY code
        ) b
 WHERE a.code = b.code
 ORDER BY a.code
;

9i 에서도 계층형쿼리 사용할 수 있을 꺼에요
select code, max(nm)as nm, max(sid)as sid, max(addr) as addr, max(국어) as 국어, max(영어) as 영어
from (
select code,nm,sid,addr, DECODE(test,'국어',SUBSTR(tt,2)) AS 국어 , DECODE(test,'영어',SUBSTR(tt,2)) AS 영어
from (
      select max(lv) over (partition by code,test) as maxlv ,lv,tt ,code ,test,nm,sid ,addr
      from (
        select a.code,a.nm,a.sid ,a.addr,level as lv  ,sys_connect_by_path(ynm||' '||tdate||' '||tscore, ',') as tt, b.test
        from a_table a
            ,b_table b
        where a.code = b.code
        connect by a.code = prior a.code 
        and        b.test = prior b.test
        and        b.ynm < prior b.ynm
      )
    )
where maxlv = lv
)
group by code;