title: ListAgg Over ListAgg - Oracle
body: <p>I wanted to get some data from multiple rows under same column (SRAV.XYZ) and concat it with other col hence used the listagg query.</p>
  <pre><code>SELECT LISTAGG (
   REGEXP_SUBSTR (SRAV.XYZ,
      '[^:]+$'),
     ';')
  WITHIN GROUP (ORDER BY
  REGEXP_SUBSTR (
  SRAV.XYZ,
  '[^:]+$')) ||';'||SRA.ABC
    /*(CASE
        WHEN SRA.ABC like 'PROF.TMP' THEN SRA.ABC = 'TMP'
        WHEN SRA.ABC like 'PROF' THEN SRA.ABC ='PROF'
        ELSE SRA.ABC='EMPLOYEES' END) */
  FROM TEST1 SPAEM,
       TEST2 SRAV,
       TEST3  srm,
       TEST4  SRA
         WHERE SRAV.RID = srm.RGID
           AND SRAV.PID IN
                ('123RTU23',
                 '456U43',
                 'AB4577Y')
          AND SRAV.XYZ IS NOT NULL
          AND SPAEM.EMPID = srm.SEC_UUID
          AND SRAV.PID = SRA.PRID
          AND SPAEM.EMPID = 139806
          group by ABC
  </code></pre>
  <p>I am able to get the output in the below format:</p>
  <pre><code>  physics;PROF.TMP
    bio;EMPLOYEES
  </code></pre>
  <p>Now, I am having 2 issues that I am unable to handle.</p>
  <ol>
  <li><p>I want the output in the below format:</p>
  <pre><code>physics;PROF.TMP,bio;EMPLOYEES
  </code></pre>
  </li>
  <li><p>My case when is not working ( hence commented ) when I am trying to concat.</p>
  </li>
  </ol>
  <p>The ideal output would be:</p>
  <pre><code>   physics;TMP,bio;EMPLOYEES
  </code></pre>
  <p>Any help in this regard.</p>
  <p>Regards,</p>