ListAgg Over ListAgg - Oracle

Question

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.

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

I am able to get the output in the below format:

  physics;PROF.TMP
  bio;EMPLOYEES

Now, I am having 2 issues that I am unable to handle.

1. I want the output in the below format:

   physics;PROF.TMP,bio;EMPLOYEES
   

2. My case when is not working ( hence commented ) when I am trying to concat.

The ideal output would be:

   physics;TMP,bio;EMPLOYEES

Any help in this regard.

Regards,

Answer 1

CASE probably doesn't work because of LIKE; the way you put it, it acts as if it was =, actually. Wildcards are missing. Also, syntax you used seems to be wrong (from my point of view). Perhaps you meant to say something like this:

CASE
  WHEN SRA.ABC like '%PROF.TMP%'   THEN 'TMP'
  WHEN SRA.ABC like '%PROF%'       THEN 'PROF'
  ELSE 'EMPLOYEES' 
END

---

As of listagg over listagg: use your current query as a subquery or as a CTE, and then apply yet another listagg:

with your_current_query as
  (select listagg(...) within group over (...) as result_1
   from ...
   where ...
  )
-- apply listagg to result_1
select listagg(result_1, ', ') over (...) as final_result
from your_current_query

---

That's theory. If you want something more, provide a simple test case.