title: Managing overflows in LISTAGG on Amazon Redshift
body: <p>Using the example from this post: <a href="https://blogs.oracle.com/datawarehousing/entry/managing_overflows_in_listagg" rel="noreferrer">https://blogs.oracle.com/datawarehousing/entry/managing_overflows_in_listagg</a></p>
  
  <p>The following statement:</p>
  
  <pre><code>SELECT
  deptno,
  LISTAGG(ename, ';') WITHIN GROUP (ORDER BY empno) AS namelist 
  FROM emp 
  GROUP BY deptno;
  </code></pre>
  
  <p>will generate the following output: </p>
  
  <pre><code>DEPTNO     NAMELIST
  ---------- ----------------------------------------
  10         CLARK;KING;MILLER
  20         SMITH;JONES;SCOTT;ADAMS;FORD
  30         ALLEN;WARD;MARTIN;BLAKE;TURNER;JAMES
  </code></pre>
  
  <p>Let’s assume that the above statement does not run and that we have a limit of 15 characters that can be returned by each row in our LISTAGG function. This is in actuality 65535 on Amazon Redshift.</p>
  
  <p>We would want the following to be returned in this case:</p>
  
  <pre><code>DEPTNO     NAMELIST
  ---------- ----------------------------------------
  10         CLARK;KING
  10         MILLER
  20         SMITH;JONES
  20         SCOTT;ADAMS
  20         FORD
  30         ALLEN;WARD
  30         MARTIN;BLAKE
  30         TURNER;JAMES 
  </code></pre>
  
  <p>What would be the best way to recreate this result in Amazon Redshift to avoid any data loss and taking speed into consideration?</p>