oracle多列传行

创建操作表
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