wm_concat问题

[zzxiaoma]

select bh,wm_concat(distinct name) aa,wm_concat(distinct id) bb from .... left join ....
类似于这样的语句查出来后name和id并不是对应的。
name id
aaa 1
bbb 2
ccc 5
ddd 3

出来后
aa bb
aaa,bbb,ccc,ddd 1,2,3,5

name和id并没有按照顺序对应，应该怎么写sql呢
结果应该是
aa bb
aaa,bbb,ccc,ddd 1,2,5,3

[虫洞]

给你个例子 select m, max(r) from (select m, wm_concat(n) over (partition by m order by n) r from t) group by m ;

[shy315]

如果name和id的distinct数量不同，也没法对应啊

[shy315]

要是不用distinct， 可以用rownum建个排序的虚拟列， wm_concat按照该列的顺序组合，就可以实现了。

[zzxiaoma]

有别的办法吗？

[forgetsam]

select distinct wm_concat(name) over(partition by .. order by .. rows between unbounded preceding and unbounded following) aa, wm_concat(name) over(partition by .. order by .. rows between unbounded preceding and unbounded following) bb from table

[babaerzi17]

sys_connect_by_path结合分析函数可以实现

[糖果茜茜]

create table t1( ID INT, NAME varchar2(10) ); insert into t1 values(1,'aaa'); insert into t1 values(2,'bbb'); insert into t1 values(5,'ccc'); insert into t1 values(3,'ddd'); commit; (一) select NAME, max(r) from (select NAME, wm_concat(ID) over (partition by NAME order by ID) r from t1) group by NAME ; （二） SELECT WMSYS.WM_CONCAT(NAME) NAME,WMSYS.WM_CONCAT(ID) ID from t1 group by NAME,ID;

[u010412956]

select listagg(ID, ',') within group(order by rownum),
       listagg(NAME, ',') within group(order by rownum)
  from tt;