如何在Oracle中合并用空格分隔的行? [英] How to concat rows separated by a space in oracle?

问题描述

我正在尝试将表中的行合并/合并为一行.我尝试使用listagg，但由于varchar限制，因此无法正常工作.

I am trying to concat/merge rows in a table to one single row. I tried using listagg but due to varchar limitation this doesn't work.

create table tmp(word VARCHAR2(4000),
                 lvl NUMBER);

insert into tmp2 values('python',1);
insert into tmp2 values('java',2);

select listagg(word,' ') within group(order by lvl) as listagg_output from tmp;

输出应类似于python java.

The output should look like python java.

推荐答案

这么长的字符串怎么办?

What will you do with such a long string?

无论如何，看看这个例子.如果 listagg 不起作用，则 xmlagg 将起作用.

Anyway, have a look at this example; if listagg won't work, xmlagg will.

SQL> create table test (id, col) as
  2  select rownum, a.column_name
  3  from user_tab_columns a cross join user_tab_columns b
  4  cross join user_tab_columns c;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
      9261

SQL> select listagg(col, ' ') within group (order by null) result from test;
select listagg(col, ' ') within group (order by null) result from test
                                                                  *
ERROR at line 1:
ORA-01489: result of string concatenation is too long


SQL> select length(xmlagg(xmlelement(e, col, ' ').extract('//text()') order by null).GetClobVal()) length_result
  2  from test;

LENGTH_RESULT
-------------
        51156

SQL>

这篇关于如何在Oracle中合并用空格分隔的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！