user3438447 user3438447 - 12 days ago 8
SQL Question

SQL concatenate one of the columns in groups of 10 with LISTAGG()

I need help writing a query that can concatenate one of the columns in groups of 10.

Example:

id1 | string1
id1 | string2
id1 | string3
...
id1 | string 1000


Since SQL do not allow too large concatenated strings I would like to maybe group 10 at a time so that I get:

id1 | string1,string2,...,string10
id1 | string11,string12,...,string20


Thanks

Answer
select      id,listagg (str,',') within group (order by str)

from       (select      id,str,row_number () over (partition by id order by str) - 1    as rn
            from        t
            ) 

group by    id,floor (rn / 10)
;

or if you don't care about the order -

select      id,listagg (str,',') within group (order by null)

from       (select      id,str,rownum - 1 as rn
            from        t
            ) 

group by    id,floor (rn / 10)
;

Sample

create table t
as
select          'id'    || to_char(ceil(level/100))     as id
                ,'str'  || lpad(to_char(level),4,'0')   as str
from            dual
connect by      level <= 1000
;