SELECT productcode, TRANSLATE (LTRIM (text, '/'), '*/', '*,') name
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY productcode ORDER BY productcode,
lvl DESC) rn,
productcode, text
FROM (SELECT productcode, LEVEL lvl,
SYS_CONNECT_BY_PATH (name,'/') text
FROM (SELECT productcode, name as name,
ROW_NUMBER () OVER (PARTITION BY productcode ORDER BY productcode,name) x
FROM busi_financing_person
ORDER BY productcode, name) a
CONNECT BY productcode = PRIOR productcode AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY productcode
如把productcode字段相同的多条记录中的name逗号隔开合到一条记录里。
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY productcode ORDER BY productcode,
lvl DESC) rn,
productcode, text
FROM (SELECT productcode, LEVEL lvl,
SYS_CONNECT_BY_PATH (name,'/') text
FROM (SELECT productcode, name as name,
ROW_NUMBER () OVER (PARTITION BY productcode ORDER BY productcode,name) x
FROM busi_financing_person
ORDER BY productcode, name) a
CONNECT BY productcode = PRIOR productcode AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY productcode
如把productcode字段相同的多条记录中的name逗号隔开合到一条记录里。