 <p>Like other aggregate functions, <a href="http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030" rel="nofollow"><code>listagg</code> also supports analytic functions</a>. So, we can partition it by values. <code>floor(rownum/50)</code> gives the same value for 50 consecutive rows.</p> <pre><code>select distinct '^.*(' || listagg(id, '|') within group (order by id) over (partition by floor(rownum / 50)) || ')' regex from mytable </code></pre>