CODEWITHSUNDEEP
sqloracle-database
Zesty
Zesty

Reputation: 2981

How can I get multiple listagg rows for a certain number of records?

As you probably know, LISTAGG allows you to concatenate values from multiple rows into a single value.

I'm trying to build a regular expression from multiple rows, and then extract it to be used in other applications to search for files.

However, there is a limit on the maximum length of regular expressions in Oracle (512 bytes).

For this reason, I need to get multiple rows with a separate listagg, and then export that output.

--The output I need is multiple rows with a listagg on 50 rows each

select '^.*(' || listagg(id, '|') within group (order by id) || ')' regex
from mytable
--where rownum < 50

Here's where I'm stuck. Is it possible?

Upvotes: 1

Views: 1585

Answers (1)

Zesty
Zesty

Reputation: 2981

Like other aggregate functions, listagg also supports analytic functions. So, we can partition it by values. floor(rownum/50) gives the same value for 50 consecutive rows.

select distinct '^.*(' || listagg(id, '|') within group (order by id)
    over (partition by floor(rownum / 50)) || ')' regex
from mytable

Upvotes: 1

Related Questions