Got a SQL from the table with 100,000 + records , one particular varchar2 field contains strings like '12345', '56789', '1111'.
I would like to create a table with split / chunks with appropriate comma seperate
example in the live link ::: select listagg(id, ',') within group (order by id) from (select course_id as id from ad.AD_STUDENT_COURSE_DETAILS)
Example :: 100 records each record has the following ids
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99
Now Assume based on the above result sets I should be able to make my sequence dynamic and insert group of records into comma separated values (listagg) , on my own definition
If I give 22 it should get values and distribute into 4 chunks and the remaining left based on the select query results and make the respective chunks of sequence. Here in this example the 99 records from my select query have been loaded into new table with 5 records (5 sequence number) each record carry the 22 records and the remaining.
New Table
seq_number list_of_ids
1 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
2 23,2425,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44
3 45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66
4 67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88
5 89,90,91,92,93,94,95,96,97,98,99
Is it possible to achieve the same in SQL query to who the results in above format or PL/SQL block?
Appreciate your help.
To split the rows into groups with N rows in each:
- Assign a row_number() to the table (if there isn't already) sequential row numbers starting at one
- Divide this row number by N, returning the ceil of it
- Group by this expression:
with rws as (
select level rn from dual
connect by level <= 100
), grps as (
select r.*,
ceil ( rn / 22 ) grp
from rws r
)
select grp,
listagg ( rn, ',' )
within group (
order by rn
) vals
from grps
group by grp;
GRP VALS
1 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
2 23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44
3 45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66
4 67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88
5 89,90,91,92,93,94,95,96,97,98,99,100