ORACLE中表 test ( a,b,c)有记录如下：
A B C
1 XXX 01
2 YYY 01
3 KKK 02
4 III 02
。。。。
我需要把C相同的B根据序号A串联起来，即得到这样的结果
B C
XXXYYY 01
KKKIII 02
。。。。
这样的SQL怎么写？是不是ORACLE中无法解决这样的问题？请赐教，谢谢
------解决方案--------------------
我昨天也碰到了这样的问题，但数据量比较大，我没有用sql语句来写，我用游标来实现，帮你顶下，我也想知道怎么做到？
------解决方案--------------------
- SQL code
--测试数据create table test(a int,b varchar2(100),c varchar2(100));insert into testselect 1,'XXX','01' from dual union all select 2,'YYY','01' from dual union allselect 3,'KKK','02' from dual union all select 4,'III','02' from dual;--执行查询select c,replace(substr(max(sys_connect_by_path(b, '|')), 2),'|','') bfrom (select b, c, row_number() over(partition by c order by 1) rnfrom test)start with rn = 1connect by rn - 1 = prior rn and c = prior cgroup by c;--查询结果c b01 XXXYYY02 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