Is it possible to use three LISTAGGs and group by elements in row?

Question

I have a situation where I would like to put three different columns, each which have multiple rows, into a single row.

I am using this code, which works to give me all of the first columns, then the second columns, then the third.

   , '(' ||(LISTAGG(billing_item_id, ', ') WITHIN GROUP (order by eduid) || ' | ' ||  LISTAGG(billing_item_amount, ', ') WITHIN GROUP (order by eduid))  || ' | ' ||  LISTAGG(REGEXP_SUBSTR(instance_name,'^[A-Z]{3,4} [0-9]{4}'), ', ') WITHIN GROUP (order by eduid) || ')' as billing

The results are:

321718277   Anon Person SENIOR HIGH SCHOOL  2022-2023   (606547, 606550 | 225, 225 | ENGL 1175, MATH 1153)

What I would like to see is

321718277   Anon Person SENIOR HIGH SCHOOL  2022-2023   (606547|225|ENGL 1175),(606550|225|MATH 1153)

The billing id, 606547, amount 225, and course ENGL 1175 are all on the same row.

Answer 1

Concatenate value within listagg, not listaggs themselves. Something like this:

SQL> with test(eduid, billing_item_id, billing_item_amount, instance_name) as
  2    (select 1, 606547, 225, 'ENGL 1175' from dual union all
  3     select 2, 606550, 225, 'MATH 1153' from dual
  4    )
  5  select listagg('(' || billing_item_id     ||'|'||
  6                        billing_item_amount ||'|'||
  7                        REGEXP_SUBSTR(instance_name,'^[A-Z]{3,4} [0-9]{4}') || ')', ',')
  8         within group (order by eduid) as billing
  9  from test;

BILLING
--------------------------------------------------------------------------------
(606547|225|ENGL 1175),(606550|225|MATH 1153)

SQL>

Answer 2

@Littlefoot. Yes. That was the syntax I needed.

 , listagg('(' || billing_item_id     ||'|'|| billing_item_amount ||'|'||  REGEXP_SUBSTR(instance_name,'^[A-Z]{3,4} [0-9]{4}') || ')', ',')  within group (order by eduid) as billing