select infocode,wm_concat(columnname),wm_concat(columncode) from m group by infocode;INFOCODE WM_CONCAT(COLUMNNAME) WM_CONCAT(COLUMNCODE) ---------- ----------------------------------------------------- --------------------- 12 股权激励对象名单,股权激励进展公告 001002007001003,001002007001007001
我也试过这样写,出来结果乱码 就是这样?Co繰盵鶎aT SU,?Co繰睆踈UQlTJ
不要用wm_concat了,12c已经被干掉了。用listagg
数据: SQL> select * from test1;
A B C ---------- ---------- ---------- 1 2 4 1 3 4 2 3 4 3 3 4 1 5 4 1 2 3 2 2 3SELECT A.A, ltrim(MAX(REPLACE(SYS_CONNECT_BY_PATH(B, '—'), '—', ',')),','), ltrim(MAX(REPLACE(SYS_CONNECT_BY_PATH(C, '—'), '—', ',')),',') FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) AS RN FROM TEST1 T) A START WITH RN = 1 CONNECT BY PRIOR RN = RN - 1 AND PRIOR A = A GROUP BY A;结果: 1 2,2,3,5 4,3,4,4 2 2,3 3,4 3 3 4
select infocode,wm_concat(columnname),wm_concat(columncode) from m group by infocode;INFOCODE WM_CONCAT(COLUMNNAME) WM_CONCAT(COLUMNCODE)
---------- ----------------------------------------------------- ---------------------
12 股权激励对象名单,股权激励进展公告 001002007001003,001002007001007001
就是这样?Co繰盵鶎aT
SU,?Co繰睆踈UQlTJ
SQL> select * from test1;
A B C
---------- ---------- ----------
1 2 4
1 3 4
2 3 4
3 3 4
1 5 4
1 2 3
2 2 3SELECT A.A,
ltrim(MAX(REPLACE(SYS_CONNECT_BY_PATH(B, '—'), '—', ',')),','),
ltrim(MAX(REPLACE(SYS_CONNECT_BY_PATH(C, '—'), '—', ',')),',')
FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) AS RN
FROM TEST1 T) A
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR A = A
GROUP BY A;结果:
1 2,2,3,5 4,3,4,4
2 2,3 3,4
3 3 4