SOURCE：
OBJECT：
用DB2临时表递归查询得到结果：
WITH n(id,str,beg_pos,end_pos) AS(
SELECT id,TEXT||',', 1,locate(',',TEXT||',') FROM TEST_
UNION ALL
SELECT id,str,end_pos+1,LOCATE(',',str,end_pos+1) FROM n
WHERE LOCATE(',',str,end_pos+1)>0
)
SELECT id,SUBSTR(str,beg_pos,end_pos-beg_pos) FROM n ORDER BY ID
========================================================================================
源表：LX_TMP1
结果集：
专用SQL：
SELECT ID,LISTAGG(OLDNAME,',')
FROM LX_TMP1
GROUP BY ID
通用SQL：
WITH LX_TMP2(ID,NEWOLDNAME,SN)AS (
SELECT ID,OLDNAME,SN
FROM
(SELECT ID,OLDNAME,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY OLDNAME ) AS SN
FROM LX_TMP1)
WHERE SN = 1
UNION ALL
SELECT T1.ID,T1.OLDNAME||','||T2.NEWOLDNAME AS NEWOLDNAME,T1.SN
FROM
(SELECT ID,OLDNAME,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY OLDNAME ) AS SN FROM LX_TMP1 ) T1,LX_TMP2 T2
WHERE T1.ID = T2.ID AND T1.SN = T2.SN + 1
)
--SELECT * FROM LX_TMP2
SELECT ID,NEWOLDNAME FROM LX_TMP2 WHERE (ID,SN) IN(SELECT ID,MAX(SN)
FROM LX_TMP2
GROUP BY ID)
原理：
