创建操作表
create table test_loge (id int,name varchar(10));
insert into test_loge values(1,'123456789');
insert into test_loge values(1,'M:987654');
oracle 11g
select tt.id,
listagg(tt.name1, ',') within group(order by tt.name1) as name_con
from (select t.id, regexp_replace(t.name, '[^0-9a-zA-Z@.]', '') name1
from test_loge t) tt
group by tt.id;
10g之前
with q as
(select ccc.id key, ccc.name x from test_loge ccc)
select key,
rtrim(xmlagg(xmlelement(e, x || ', ') order by x).extract('//text()')) as concatval,
xmlagg(xmlelement("test", x || ', ') order by x).extract('//text()'),
xmlagg(xmlelement("test", x || ', ') order by x).extract('/test/text()'),
xmlagg(xmlelement("test", x || ', ') order by x)
from q
group by key
另一方法
select wmsys.wm_concat(name) from test_loge group by id
drop table test_loge