问题:在开发过程中需要实现以下效果
1 a
1 b ------> 1 a,b,c
1 c
1.oracle 10以上可以使用wmsys.wm_concat函数,相当Easy
2.如果Oracle是9以下,则用Procedure或Function实现
3.纯SQL实现:(Oracle9及以上 由于有 SYS_CONNECT_BY_PATH函数)
SELECT REPAIR_REC_SYS_ID, TRANSLATE (LTRIM (text, '/'), '*/', '*,') researcherList
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY REPAIR_REC_SYS_ID ORDER BY REPAIR_REC_SYS_ID,
lvl DESC) rn,
REPAIR_REC_SYS_ID, text
FROM (SELECT REPAIR_REC_SYS_ID, LEVEL lvl,
SYS_CONNECT_BY_PATH (GOOD_PART,'/') text
FROM (SELECT REPAIR_REC_SYS_ID, GOOD_PART as GOOD_PART,
ROW_NUMBER () OVER (PARTITION BY REPAIR_REC_SYS_ID ORDER BY REPAIR_REC_SYS_ID,GOOD_PART) x
FROM REPAIR_REPL_DETAIl
ORDER BY REPAIR_REC_SYS_ID, GOOD_PART) a
CONNECT BY REPAIR_REC_SYS_ID = PRIOR REPAIR_REC_SYS_ID AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY REPAIR_REC_SYS_ID
REPAIR_REC_SYS_ID:你需要合并的参照字段
GOOD_PART:需要合并的字段
REPAIR_REPL_DETAIl:表名