SELECT Q_ID, LTRIM(MAX(SYS_CONNECT_BY_PATH(A.K_ID, ' '))) STR FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY Q_ID ORDER BY Q_ID) PREV_SEQ, ROW_NUMBER() OVER (PARTITION BY Q_ID ORDER BY Q_ID)+1 CURR_SEQ, Q_ID, K_ID FROM YOURTABLE ) A START WITH A.PREV_SEQ = 1 CONNECT BY PRIOR A.CURR_SEQ = A.PREV_SEQ GROUP BY Q_ID;
SQL> with a as (select '01' Q_ID,'01' K_ID FROM DUAL 2 UNION 3 select '01' Q_ID,'02' K_ID FROM DUAL 4 UNION 5 select '01' Q_ID,'03' K_ID FROM DUAL 6 UNION 7 select '02' Q_ID,'01' K_ID FROM DUAL 8 UNION 9 select '02' Q_ID,'02' K_ID FROM DUAL 10 ) 11 select Q_ID,WMSYS.WM_CONCAT(K_ID) K_ID FROM A 12 GROUP BY Q_ID 13 /
SQL> with a as (select '01' Q_ID,'01' K_ID FROM DUAL 2 UNION 3 select '01' Q_ID,'02' K_ID FROM DUAL 4 UNION 5 select '01' Q_ID,'03' K_ID FROM DUAL 6 UNION 7 select '02' Q_ID,'01' K_ID FROM DUAL 8 UNION 9 select '02' Q_ID,'02' K_ID FROM DUAL 10 ) 11 select Q_ID,SUBSTR(MAX(SYS_CONNECT_BY_PATH(K_ID,',')),2) K_ID 12 FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Q_ID ORDER BY Q_ID) RN, 13 Q_ID, K_ID 14 FROM A) A 15 START WITH RN=1 16 CONNECT BY RN-1=PRIOR RN 17 GROUP BY Q_ID 18 /
SELECT Q_ID, LTRIM(MAX(SYS_CONNECT_BY_PATH(A.K_ID, ' '))) STR
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Q_ID ORDER BY Q_ID) PREV_SEQ,
ROW_NUMBER() OVER (PARTITION BY Q_ID ORDER BY Q_ID)+1 CURR_SEQ,
Q_ID, K_ID
FROM YOURTABLE
) A
START WITH A.PREV_SEQ = 1
CONNECT BY PRIOR A.CURR_SEQ = A.PREV_SEQ
GROUP BY Q_ID;
2 UNION
3 select '01' Q_ID,'02' K_ID FROM DUAL
4 UNION
5 select '01' Q_ID,'03' K_ID FROM DUAL
6 UNION
7 select '02' Q_ID,'01' K_ID FROM DUAL
8 UNION
9 select '02' Q_ID,'02' K_ID FROM DUAL
10 )
11 select Q_ID,WMSYS.WM_CONCAT(K_ID) K_ID FROM A
12 GROUP BY Q_ID
13 /
Q_ID K_ID
---- --------------------------------------------------------------------------------
01 01,02,03
02 01,02
看哈這個是不是簡單點啊,不過版本控制在10G及以上的版本上
2 UNION
3 select '01' Q_ID,'02' K_ID FROM DUAL
4 UNION
5 select '01' Q_ID,'03' K_ID FROM DUAL
6 UNION
7 select '02' Q_ID,'01' K_ID FROM DUAL
8 UNION
9 select '02' Q_ID,'02' K_ID FROM DUAL
10 )
11 select Q_ID,SUBSTR(MAX(SYS_CONNECT_BY_PATH(K_ID,',')),2) K_ID
12 FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Q_ID ORDER BY Q_ID) RN,
13 Q_ID, K_ID
14 FROM A) A
15 START WITH RN=1
16 CONNECT BY RN-1=PRIOR RN
17 GROUP BY Q_ID
18 /
Q_ID K_ID
---- --------------------------------------------------------------------------------
01 01,02,03
02 01,02
SYS_CONNECT_BY_PATH() 会报字符超长错误的。