Oracle-行合併

通常使用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