WITH TT AS
(SELECT 1 ID
FROM DUAL
UNION ALL
SELECT 2 ID
FROM DUAL
UNION ALL
SELECT 3 ID
FROM DUAL
UNION ALL
SELECT 4 ID
FROM DUAL),
T AS
(SELECT 1 ID, 'ZS' NM
FROM DUAL
UNION ALL
SELECT 2 ID, 'ZS' NM
FROM DUAL
UNION ALL
SELECT 2 ID, 'LS' NM
FROM DUAL
UNION ALL
SELECT 3 ID, 'ZS' NM
FROM DUAL
UNION ALL
SELECT 3 ID, 'WW' NM
FROM DUAL)
SELECT TT.ID, T3.NM
FROM TT,
(SELECT T2.ID, ltrim(max(SYS_CONNECT_BY_PATH(t2.NM, '||')), '||') NM
FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) RN
FROM T) T2
connect by prior rn = rn - 1
and prior t2.id = t2.id
GROUP BY T2.ID) T3
WHERE TT.ID = T3.ID(+);
WITH A AS
(SELECT 1 ID FROM DUAL UNION ALL
SELECT 2 ID FROM DUAL UNION ALL
SELECT 3 ID FROM DUAL ),
B AS
(SELECT 1 ID, '张三' name FROM DUAL UNION ALL
SELECT 2, '李四' FROM DUAL UNION ALL
SELECT 2, '张三' FROM DUAL UNION ALL
SELECT 3, '王五' FROM DUAL UNION ALL
SELECT 3, '李四' FROM DUAL)
SELECT A.ID, REPLACE(WMSYS.WM_CONCAT(B.NAME), ',', '|')
FROM A, B
WHERE A.ID = B.ID(+)
GROUP BY A.ID
from 表A a,
(select id, replace(wm_concat(name), ',', '||') as name
from 表B
group by id) b
where a.id = b.id;
from (select a.id, b.name from a, b where a.id = b.id) t
group by id
select t2.id id, replace(wm_concat(t2.name),',',' | ') name from t_test2 t2,t_test1 t1 where t1.id=t2.id(+) group by t2.id;