现有一张表t1:
结构数据大致如下:
c1, c2, c3
1, A, a
1, A, b
1, B, c
2, A, d
2, A, e我想得到如下结果:1, A, ab
1, B, c
2, A, de不要用 wmsys.wm_concat, 最好一条SELECT 搞定。麻烦哪位大神给个答案!!
谢谢!~
结构数据大致如下:
c1, c2, c3
1, A, a
1, A, b
1, B, c
2, A, d
2, A, e我想得到如下结果:1, A, ab
1, B, c
2, A, de不要用 wmsys.wm_concat, 最好一条SELECT 搞定。麻烦哪位大神给个答案!!
谢谢!~
需要多一步排序子查询用wmsys.wm_concat 不能确定结果的顺序
用sys_connect_by_path可以更加灵活掌握with a as
(select 1 c1,'A' c2,'a' c3 from dual union all
select 1 c1,'A' c2,'b' c3 from dual union all
select 1 c1,'B' c2,'c' c3 from dual union all
select 2 c1,'A' c2,'d' c3 from dual union all
select 2 c1,'A' c2,'e' c3 from dual
)
select c1,c2,replace(max(sys_connect_by_path(c3,',')),',','')
from (select a.*,row_number() over(partition by c1,c2 order by c3) rn from a) t
start with t.rn=1
connect by prior c1= c1 and prior c2= c2 and prior rn= rn-1
group by c1,c2
;
with a as
(select 1 c1,'A' c2,'a' c3 from dual union all
select 1 c1,'A' c2,'b' c3 from dual union all
select 1 c1,'B' c2,'c' c3 from dual union all
select 2 c1,'A' c2,'d' c3 from dual union all
select 2 c1,'A' c2,'e' c3 from dual
)SELECT c2,LISTAGG(c3,',')WITHIN GROUP(order by c1 ) as c4 from a group by c1 ,c2这样写报错啊!未找到from。。