with table1 as ( select 001 FcaID,'卡片1' FName from dual union all select 001 FcaID,'卡片2' FName from dual union all select 002 FcaID,'卡片3' FName from dual union all select 002 FcaID,'卡片4' FName from dual union all select 002 FcaID,'卡片4' FName from dual ) select FcaID,listagg(FName, '') within group(order by rownum),replace(to_char(wm_concat(FName)),',','') from table1 group by FcaID
with table1 as (select 001 FcaID, '卡片1' FName from dual union all select 001 FcaID, '卡片2' FName from dual union all select 002 FcaID, '卡片3' FName from dual union all select 002 FcaID, '卡片4' FName from dual union all select 002 FcaID, '卡片4' FName from dual) SELECT A.FcaID, MAX(REPLACE(SYS_CONNECT_BY_PATH(FName, '—'), '—', '')) FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY FcaID ORDER BY FName) AS RN FROM table1 T) A START WITH RN = 1 CONNECT BY PRIOR RN = RN - 1 AND PRIOR FcaID = FcaID GROUP BY FcaID;
[ ------解决方案--------------------
select FcaID,listagg(FName,'') within group(order by FcaID) as newcol from table1 group by FcaID / ------解决方案-------------------- concat有什么问题,多了个分隔的逗号么,那在处理下啊,listagg要11g的版本