将具有相同的主键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,
INVT_PROD_ID
From V_J_B_CLAUSE
where INVT_PROD_ID In
(Select INVT_PROD_ID
From V_J_B_CLAUSE
Group By INVT_PROD_ID
Having Count(*) > 1)) A) AA) AAA
CONNECT BY PRIOR rchild = rfather
START WITH rfather LIKE '' %0 '') AAAA
group by INVT_PROD_ID
union
Select to_char(INVT_PROD_ID),
to_char(CLAUSE_MEMO),
to_char(CLAUSE_TP_CD),
to_char(BBCK_DT)
From V_J_B_CLAUSE
where INVT_PROD_ID In (Select INVT_PROD_ID
From V_J_B_CLAUSE
Group By INVT_PROD_ID
Having Count(*) = 1)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
