Is there a way to continue on the next row after listagg() runs out of space (>4k)?

I would like to change a table that has a number of values per week, to having all values in one row per week. But when I use listagg() I run out of space. I can’t change the maximum string size (ALTER SYSTEM SET max_string_size=extended SCOPE= SPFILE), and I don’t want to truncate the resulting string. However I could just continue on the next line, but I’m not sure how to. For example, if my input is:

Week                   SKU
202001                 598441
202001                 846541
202001                 77557
202001                 57813
202001                 5741651
202001                 21684135
202001                 5451516
202001                 545129
202001                 98754123
202001                 5644242
202002                 68454155
...

​x
 
Week                   SKU202001                 598441202001                 846541202001                 77557202001                 57813202001                 5741651202001                 21684135202001                 5451516202001                 545129202001                 98754123202001                 5644242202002                 68454155...​

Assuming the input file is long enough that it will trip the error :

01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.

 
01489. 00000 -  "result of string concatenation is too long"*Cause:    String concatenation result is more than the maximum size.*Action:   Make sure that the result is less than the maximum size.​

Then I want a piece of code similar to this:

select 
  weeknr, 
  ''''||listagg(sku, ''',''', ON OVERFLOW NEXT LINE) within group(order by weeknr)||'''' sku_numbers
from 
  input_table
group by 
  weeknr

 
select   weeknr,   ''''||listagg(sku, ''',''', ON OVERFLOW NEXT LINE) within group(order by weeknr)||'''' sku_numbersfrom   input_tablegroup by   weeknr​

So that my output will look like this:

Week                   SKU
202001                 '598441','846541','77557','57813','5741651','21684135'
202001                 '5451516','545129','98754123','5644242'
202002                 '68454155',...
...

 
Week                   SKU202001                 '598441','846541','77557','57813','5741651','21684135'202001                 '5451516','545129','98754123','5644242'202002                 '68454155',......​

Is that possible? Any help is greatly appreciated!

Answer

You can use the analytical function to find the SUM of the length of the strings and then use it for sub-grouping as follows:

SELECT WEEKNR,
       '''' || LISTAGG(SKU, ''',''') WITHIN GROUP(ORDER BY WEEKNR) || '''' SKU_NUMBERS
  FROM (
    SELECT WEEKNR,
           FLOOR(SUM(LENGTH(SKU) + 3) 
                   OVER(PARTITION BY WEEKNR ORDER BY SKU) / 4000) AS GROUP_NUMBER,
           SKU
      FROM INPUT_TABLE
)
 GROUP BY WEEKNR, GROUP_NUMBER

 
SELECT WEEKNR,       '''' || LISTAGG(SKU, ''',''') WITHIN GROUP(ORDER BY WEEKNR) || '''' SKU_NUMBERS  FROM (    SELECT WEEKNR,           FLOOR(SUM(LENGTH(SKU) + 3)                    OVER(PARTITION BY WEEKNR ORDER BY SKU) / 4000) AS GROUP_NUMBER,           SKU      FROM INPUT_TABLE) GROUP BY WEEKNR, GROUP_NUMBER​

Advertisement

Answer