Geek asks and answers

String concatenation in CLOB

There are many similar questions on this, but I cannot find any solutions that take into account everything that is too large for the varchar2 result.

So what I'm trying to do is:

Column1 | Column2
-------- --------
1         Hello
1         world,
1         please help
2         Thanks
2         world,
2         you're the best.

In it:

Column1 | Column2
-------- --------
1         Hello world, please help
2         Thanks world, you're the best.

My particular problem is that there are several cases where the new concatenated value exceeds 4000 characters, so I cannot use LISTAGGas I hoped. I am especially interested in solutions without having to write a function, but will do it.

+5
source share
3 answers
SELECT Column1 , LISTAGG(Column2, ' ') 
WITHIN GROUP (ORDER BY Column2) AS employees
FROM   Table1
GROUP BY Column1 ;

Please take a look at this article.

+1
source

de.hh.holger, LISTAGG WITH CLOB? STRING AGGREGATION, 4000 XMLAGG, .

, :

SELECT
   table_row_id,
   DBMS_XMLGEN.CONVERT (
     EXTRACT(
       xmltype('<?xml version="1.0"?><document>' ||
               XMLAGG(
                 XMLTYPE('<V>' || DBMS_XMLGEN.CONVERT(data_value)|| '</V>')
                 order by myOrder).getclobval() || '</document>'),
               '/document/V/text()').getclobval(),1) AS data_value
FROM (
   SELECT 1 myOrder, 1 table_row_id,'abcdefg>' data_value FROM dual
   UNION ALL
   SELECT 2, 1 table_row_id,'hijklmn' data_value FROM dual
   UNION ALL
   SELECT 3, 1 table_row_id,'opqrst' data_value FROM dual
   UNION ALL
   SELECT 4, 1 table_row_id,'uvwxyz' data_value FROM dual)
GROUP BY
   table_row_id
+2

Check out LISTAGG WITH CLOB? STRING AGGREGATION EXCEEDS 4,000 CHARACTERS WITH XMLAGG. This is a bit strange, but it seems to work.

Bye Holger

0
source

More articles:


All Articles