Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, John.

Asked: September 23, 2020 - 5:58 am UTC

Answered by: Chris Saxon - Last updated: September 28, 2020 - 11:34 am UTC

Category: SQL - Version: 11g

Viewed 100+ times

You Asked

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.

with LiveSQL Test Case:

and we said...

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 

and you rated our response

  (2 ratings)

Reviews

September 23, 2020 - 3:11 pm UTC

Reviewer: Kenyjohn from USA

Thanks it works perfect for numbers ! however the scenario initially provided is for string field so the Ceil function is throwing invalid number exception while grouping the string id's into chunks of comma seperated lists.
Chris Saxon

Followup  

September 23, 2020 - 5:42 pm UTC

So you need to do this first:

Assign a row_number() to the table (if there isn't already) sequential row numbers starting at one

Worked as expected for String

September 25, 2020 - 4:23 am UTC

Reviewer: kenyjohn from USA

Thank you including the Row_Number () function worked , one another limitation noticed was when I increased the number of records in the grouping it throw below exception with regard to listagg function even though the list of strings is stored in CLOB field.

ORA-01489: result of string concatenation is too long.

So I tried using the below function as recommended again it throws different exception.

rtrim (xmlagg (xmelement(e, rn || ',')).extract ('//text()'), ',')

It throws ORA-19011: Character string buffer too small.

Then tried with the .getClob and it worked as expected as recommended in the below blog.

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9537272200346908059


Thanks
Chris Saxon

Followup  

September 28, 2020 - 11:34 am UTC

Glad you got it sorted

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.