CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Kar
Kar

Reputation: 1014

Rows into Comma Separated Values based on number of values

As there is a limitation with a length of list values

I need to convert the rows into a list of values of max 2400 rows and remaining into next list of values

Is this possible in SQL?

The below query converts all the rows into one single list.API does not accept if the length of the list of values is beyond 2400 values 

       select listagg(id, ',')  as id_list from table;

Example

If a table has 30 rows, then first 10 should go into one list of values, second 10 rows should go into second list of values and remaining values into the last list of values

Upvotes: 1

Views: 211

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25928

with data as (
    select row_number()over(order by seq8()) as rn
    from table(generator(rowcount=>10000))
)
select listagg(rn, ',') as list
from data;

show there is no limit.. but that not so useful.

so to do the bucketing you want

with data as (
    select row_number()over(order by seq8()) as rn
    from table(generator(rowcount=>10000))
)
select listagg(rn, ',') within group (order by rn) as list
from data
group by floor(rn/2400)
order by 1;

gives you five rows (truncation done by me, just to show how the data partitioned as you asked)

LIST
1,2,3,4,5,6,7,8,9,10,11,12,13,...
2400,2401,2402,2403,2404,2405,...
4800,4801,4802,4803,4804,4805,...
7200,7201,7202,7203,7204,7205,...
9600,9601,9602,9603,9604,9605,...

Upvotes: 1

Related Questions