方法一 SYS_CONNECT_BY_PATH
WITH QA AS
(SELECT 'Row1' DROW, 'user1' EMP, 100 NUM FROM DUAL
UNION ALL
SELECT 'Row2' DROW, 'user1' EMP, 90 NUM FROM DUAL
UNION ALL
SELECT 'Row3' DROW, 'user1' EMP, 90 NUM FROM DUAL
UNION ALL
SELECT 'Row4' DROW, 'user1' EMP, 80 NUM FROM DUAL),
QB AS (SELECT EMP,NUM, COUNT(*) OVER (PARTITION BY EMP ) CNT, ROW_NUMBER() OVER (PARTITION BY EMP ORDER BY NUM) SEQ FROM QA )
SELECT EMP, SUBSTR(SYS_CONNECT_BY_PATH( NUM, ','), 2) COMBINE FROM QB
WHERE SEQ = CNT START WITH SEQ = 1 CONNECT BY PRIOR SEQ + 1 = SEQ AND PRIOR EMP=EMP;
方法二 Listagg
WITH QA AS
(SELECT 'Row1' DROW, 'user1' EMP, 100 NUM FROM DUAL
UNION ALL
SELECT 'Row2' DROW, 'user1' EMP, 90 NUM FROM DUAL
UNION ALL
SELECT 'Row3' DROW, 'user1' EMP, 90 NUM FROM DUAL
UNION ALL
SELECT 'Row4' DROW, 'user1' EMP, 80 NUM FROM DUAL)
SELECT EMP, LISTAGG(NUM, ',') WITHIN GROUP (ORDER BY NUM) AS COMBINE FROM QA group by EMP
