WITH A AS ( 
SELECT 100 NO , '한국' NM FROM DUAL UNION ALL
SELECT 101 NO , '중국' NM FROM DUAL UNION ALL
SELECT 102 NO , '미국' NM FROM DUAL UNION ALL
SELECT 103 NO , '일본' NM FROM DUAL 
) , B AS (
SELECT 100 NO , 1000 CITYNUM FROM DUAL UNION ALL
SELECT 100 NO , 1001 CITYNUM FROM DUAL UNION ALL
SELECT 101 NO , 1021 CITYNUM FROM DUAL UNION ALL
SELECT 102 NO , 1025 CITYNUM FROM DUAL UNION ALL
SELECT 103 NO , 1026 CITYNUM FROM DUAL 
) , C AS (
SELECT 1000 CITYNUM , '서울' CITY FROM DUAL UNION ALL
SELECT 1001 CITYNUM , '경기' CITY FROM DUAL UNION ALL
SELECT 1021 CITYNUM , '북경' CITY FROM DUAL UNION ALL
SELECT 1025 CITYNUM , '워싱턴' CITY FROM DUAL UNION ALL
SELECT 1026 CITYNUM , '도쿄' CITY FROM DUAL 
)

SELECT A.NO , A.NM  , LISTAGG(C.CITY,',') WITHIN GROUP (ORDER BY C.CITYNUM ) CITY 
  FROM A
     , B
     , C
 WHERE A.NO      = B.NO
   AND B.CITYNUM = C.CITYNUM
 GROUP BY A.NO , A.NM