Oracle中SYS_CONNECT_BY_PATH函数的妙用 ;

1 create table test (a varchar2(10),b varchar2(10));  
2  
3 INSERT INTO TEST (A, B) VALUES (‘1‘, ‘A‘);  
4 INSERT INTO TEST (A, B) VALUES (‘1‘, ‘B‘);  
5 INSERT INTO TEST (A, B) VALUES (‘2‘, ‘X‘);  
6 INSERT INTO TEST (A, B) VALUES (‘2‘, ‘Y‘);  
7 
8 SELECT A, B FROM TEST  ;

1 SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ‘ ‘)), ‘,‘) B
2   FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN
3           FROM TEST)
4  START WITH RN = 1
5 CONNECT BY RN - 1 = PRIOR RN
6        AND A = PRIOR A
7  GROUP BY A;