将具有相同的主键INVT_PROD_ID值,其他列值CLAUSE_MEMO,CLAUSE_TP_CD,BBCK_DT使用||进行合并,形成一条记录:
select INVT_PROD_ID,
MAX(subStr(CLAUSE_MEMO, 3)) CLAUSE_MEMO,
MAX(subStr(CLAUSE_TP_CD, 3)) CLAUSE_TP_CD,
MAX(subStr(BBCK_DT, 3)) BBCK_DT
from (SELECT INVT_PROD_ID,
sys_connect_by_path(CLAUSE_MEMO, '' || '') CLAUSE_MEMO,
sys_connect_by_path(CLAUSE_TP_CD, '' || '') CLAUSE_TP_CD,
sys_connect_by_path(BBCK_DT, '' || '') BBCK_DT
FROM (SELECT INVT_PROD_ID,
CLAUSE_MEMO,
CLAUSE_TP_CD,
BBCK_DT,
INVT_PROD_ID || rn rchild,
INVT_PROD_ID || (rn - 1) rfather
FROM (SELECT INVT_PROD_ID,
CLAUSE_MEMO,
CLAUSE_TP_CD,
BBCK_DT,
row_number() over(PARTITION BY A.INVT_PROD_ID ORDER BY A.CLAUSE_TP_CD) rn
FROM (Select CLAUSE_MEMO,
CLAUSE_TP_CD,
BBCK_DT,
I