日期:2014-05-17 浏览次数:21097 次
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;