Oracle SQL – create string by concatenating rows but does not exceed X number of characters

First I am trying to concatenate rows into string separated by comma. Second I do not want the string to be exceeded X number of character. If going to exceed then create another string from next row onward without slitting the character from each rows. I am trying to achieve this through oracle SQL.

I am looking at LISTAGG which give me the concatenated string but I have not idea how I can split by X number of characters without splitting a values from each rows, let’s say my X = 15 characters. What I get using the below code is

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20

select listagg(x, ',') within group(order by x)
from (select level x from dual
      connect by level <= 20
      )
;

My expected output will be,

row 1: 1,2,3,4,5,6,7,8 (15 characters)

row 2: 9,10,11,12,13 (13 characters because that going to exceed 15 if I add value from next row)

row 3: 14,15,16,17,18 (14 characters)

row 4: 19,20 (5 characters)

I am still not sure this can be achieved, any suggestion or hint will be most appreciated.

Answer

I tried the following query and achieved the desired result.

SELECT
    LISTAGG(X, ',') WITHIN GROUP(
        ORDER BY
            X
    ) as result
FROM
    (
        SELECT
            X,
            SUM(LENGTH(X)) OVER(
            ORDER BY
                X
            ) LENGTH_X,
            COUNT(1) OVER(
            ORDER BY
                X
            ) - 1 AS COMMAS
        FROM
            (
                SELECT
                    LEVEL   X
                FROM
                    DUAL
                CONNECT BY
                    LEVEL <= 20
            )
    )
GROUP BY
    CEIL((LENGTH_X + COMMAS) / 15);

Output:

db<>fiddle demo

Cheers!!