CREATE TABLE TEST (A VARCHAR2(10),B VARCHAR2(10),C VARCHAR2(10),D VARCHAR2(10));
INSERT INTO TEST VALUES ('a1','b1','c1','1');
INSERT INTO TEST VALUES ('a2','b2','c2','5');
INSERT INTO TEST VALUES ('a2','b2','c3','4');
INSERT INTO TEST VALUES ('a3','b1','c1','3');
SELECT DECODE(rn,1,a),DECODE(rn,1,b),DECODE(rn,1,c,d)
FROM ( SELECT a,b,MAX(c) c,MAX(d) d
FROM (SELECT a,b
,REPLACE(wmsys.wm_concat(c) OVER(PARTITION BY a,b ORDER BY c),' ') AS c
,REPLACE(wmsys.wm_concat(d) OVER(PARTITION BY a,b ORDER BY c),' ') AS d
FROM test
)
GROUP BY a,b
)
,(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=2)
ORDER BY a,b,rn;
|