Concatenate certain number of rows and split into different groups

Question

I have a query where I am concatenating some ids,but I only want to concatenate 900 and then create another row for the remaining for that same group

Select location_id,RTRIM(XMLCAST(XMLAGG(XMLELEMENT(E,'"'||client_id||'",')) AS CLOB),',') AS clientid from tab
GROUP BY location_id

Expected Output :

   Location_id  Clientid
    =========    =========
    100          a12,a13,a16,a17,b13,b18...
    200          c12,c34,c67,c33,c89,...if this has more than 900 ids then need another row for 200
    200          remaining ids comma separated

Answer 1

You can use a subquery against your real table to put the client IDs into buckets, and then include the bucket in the grouping:

select location_id,
  RTRIM(XMLCAST(XMLAGG(XMLELEMENT(E,'"'||client_id||'",')) AS CLOB),',') AS clientid
from (
  select location_id,
    client_id,
    ceil(row_number() over (partition by location_id order by client_id) / 900) as bucket
  from tab
)
group by location_id, bucket

db<>fiddle (split after 9 instead of 900 to demonstrate the principal)