SELECT ITEM_CD,GROUP_CD,RECORD_DATE,LOCALE_CD,max(substr(CODE_NAME,2)) AS FOREIGN_NAME FROM (SELECT ITEM_CD,GROUP_CD,RECORD_DATE,LOCALE_CD,sys_connect_by_path(CODE_NAME,',') AS CODE_NAME FROM (SELECT ITEM_CD,GROUP_CD,RECORD_DATE,CODE_NAME,LOCALE_CD,ITEM_CD||GROUP_CD||RECORD_DATE||LOCALE_CD||rn rchild,ITEM_CD||GROUP_CD||RECORD_DATE||LOCALE_CD||(rn-1) rfather FROM ( SELECT ITEM_CD,GROUP_CD,RECORD_DATE,CODE_NAME,LOCALE_CD,row_number() over (PARTITION BY ITEM_CD,GROUP_CD,RECORD_DATE,LOCALE_CD ORDER BY CODE_NAME) rn FROM TTP_S_T_ITEM_FOREIGN INNER JOIN TTP_S_M_GENERAL_CODE ON TTP_S_T_ITEM_FOREIGN.CODE=TTP_S_M_GENERAL_CODE.DETAIL_CD AND TTP_S_M_GENERAL_CODE.CODE_SECTION='10130' ) ) CONNECT BY PRIOR rchild=rfather START WITH rfather LIKE '%0') GROUP BY ITEM_CD,GROUP_CD,RECORD_DATE,LOCALE_CD