WITH T AS (SELECT 'table1' NAME, 'a' COL, 2 PORTION FROM DUAL UNION ALL SELECT 'table1' NAME, 'b' COL, 3 PORTION FROM DUAL UNION ALL SELECT 'table1' NAME, 'c' COL, 1 PORTION FROM DUAL UNION ALL SELECT 'table2' NAME, 'a' COL, 2 PORTION FROM DUAL UNION ALL SELECT 'table2' NAME, 'b' COL, 1 PORTION FROM DUAL) SELECT T2.NAME, LTRIM(MAX(SYS_CONNECT_BY_PATH(T2.COL, ',')), ',') COL FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY T.NAME ORDER BY T.PORTION) AS RN FROM T) T2 START WITH RN = 1CONNECT BY PRIOR RN = RN - 1 AND PRIOR T2.NAME = T2.NAME GROUP BY T2.NAME;
select name,max(mm) from (select name, wm_concat(colnum) over(partition by name order by portion) mm from t) group by name
(SELECT 'table1' NAME, 'a' COL, 2 PORTION
FROM DUAL
UNION ALL
SELECT 'table1' NAME, 'b' COL, 3 PORTION
FROM DUAL
UNION ALL
SELECT 'table1' NAME, 'c' COL, 1 PORTION
FROM DUAL
UNION ALL
SELECT 'table2' NAME, 'a' COL, 2 PORTION
FROM DUAL
UNION ALL
SELECT 'table2' NAME, 'b' COL, 1 PORTION
FROM DUAL)
SELECT T2.NAME, LTRIM(MAX(SYS_CONNECT_BY_PATH(T2.COL, ',')), ',') COL
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY T.NAME ORDER BY T.PORTION) AS RN
FROM T) T2
START WITH RN = 1CONNECT BY PRIOR RN = RN - 1
AND PRIOR T2.NAME = T2.NAME
GROUP BY T2.NAME;
from (select name,
wm_concat(colnum) over(partition by name order by portion) mm
from t)
group by name