主页 / dba / 问题 / 193565
Accepted
pgfitzpatrick
Asked: 2017-12-20 12:39:24 +0800 CST

合并单元格中具有逗号分隔值的单元格的 Oracle XMLAGG 问题

  • 772

有一种情况，我正在尝试使用 xmlagg 来获取：

     | ID | name
row1 | 1  | apple
row2 | 1  | apple,pear,banana
row3 | 1  | apple,banana
row4 | 1  | pear,banana,orange
row5 | 2  | orange
row6 | 2  | orange,pear
row7 | 2  | pear, banana

至：

1 | apple,pear,banana,orange
2 | orange,pear,banana

这可能使用 xmlagg 吗？谢谢

oracle csv

1 个回答

  1. Best Answer

    测试数据：

    select * from t_ ;

C1   C2   C3
row1 1    apple
row2 1    apple,pear,banana
row3 1    apple,banana
row4 1    pear,banana,orange
row5 2    orange
row6 2    orange,pear
row7 2    pear,banana

    询问：

    select 
c2 ||' | '|| rtrim ( 
   xmlagg( xmlelement( e, c3 || ',' ) ).extract('//text()')
, ','
) result
from
  (
    select distinct c2, regexp_substr(c3,'[^,]+', 1, level ) c3 from t_
    connect by regexp_substr(c3, '[^,]+', 1, level) is not null
  )
group by c2
;

RESULT
1 | apple,pear,orange,banana
2 | pear,orange,banana

    LISTAGG() 也可以工作 -

    select 
c2 ||' | '|| rtrim ( 
   listagg(c2 , ',') within group ( order by c2 )
) result
from
  (
    select distinct c2, regexp_substr(c3,'[^,]+', 1, level ) c3 from t_
    connect by regexp_substr(c3, '[^,]+', 1, level) is not null
  )
group by c2
;

RESULT
1 | apple,banana,orange,pear
2 | banana,orange,pear

    Dbfiddle在这里。

    • 0

相关问题