--测试数据
create table test(a int,b varchar2(100),c varchar2(100));
insert into test
select 1,'XXX','01' from dual union all  
select 2,'YYY','01' from dual union all
select 3,'KKK','02' from dual union all 
select 4,'III','02' from dual;
--执行查询
select c,replace(substr(max(sys_connect_by_path(b, '|')), 2),'|','') b
from (select b, c, row_number() over(partition by c order by 1) rn
from test)
start with rn = 1
connect by rn - 1 = prior rn and c = prior c
group by c;
--查询结果
c     b
01    XXXYYY
02    KKKIII

------解决方案--------------------
SQL> WITH A AS (SELECT 1 A,'XXX' B,'01' C FROM DUAL
 2             UNION
 3             SELECT 2 A,'YYY' B,'01' C FROM DUAL
 4             UNION
 5             SELECT 3 A,'KKK' B,'02' C FROM DUAL
 6             UNION
 7             SELECT 4 A,'III' B,'02' C FROM DUAL
 8             )
 9  select C,MAX(SYS_CONNECT_BY_PATH(B,' ')) B FROM
  10  (SELECT A,B,C,ROW_NUMBER()OVER(PARTITION BY C ORDER BY C) RN FROM A)
  11  GROUP BY C
  12  START WITH RN=1
  13  CONNECT BY RN-1=PRIOR RN AND C=PRIOR C
  14  ;
  
C  B
-- --------------------------------------------
01  XXX YYY
02  KKK III