通常使用listagg
即可,若合併后的字符串過長,可使用xmlagg
- 示例1
with
tmp_table
as(
select '1' col1 from dual
union all
select '2' col1 from dual
union all
select '3' col1 from dual
)
select listagg(col1,',') within group(order by col1) result
from tmp_table
- 示例2
with
tmp_table
as(
select 'a' name, '1' col1 from dual
union all
select 'a' name, '2' col1 from dual
union all
select 'a' name, '3' col1 from dual
union all
select 'b' name, '1' col1 from dual
union all
select 'b' name, '2' col1 from dual
)
select name, listagg(col1,',') within group(order by col1) result
from tmp_table
group by name
- 示例3
with
tmp_table
as(
select '1' col1 from dual
union all
select '2' col1 from dual
union all
select '3' col1 from dual
)
select xmlagg(xmlparse(content col1||',' wellformed) order by col1).getclobval() result
from tmp_table