I need a PL/SQL statement where it can concatenate list of individual rows to the string based on the number of rows. EX: for each 5 rows to 1 string

Question

**My select statement returns **

ID1
ID2
ID3
ID4
ID5
ID6
ID7

. . . and so on My Output should be

ID1,ID2
ID3,ID4
ID4,ID5
ID5,ID6
ID7

. . . I have tried Group BY , List agg and rownum functions but it did not work. I use a SQL servlet with readonly access.

Answer 1

To group successive rows, use CEIL(id/2):

with cte as (
  select col1, CEIL(row_number() over (order by col1)/2) as grp
  from mytable
)
select LISTAGG(col1, ',')
from cte
group by grp
order by grp

Result :

LIST
ID1,ID2
ID3,ID4
ID5,ID6
ID7

Demo here