1

I have a table like this:

CUST_NUMBER | CUST_NAME | CUST_PROFESSION
------------------------------------------  
   1        |   Mike    |    Teacher
   1        |   Mike    |    Engineer
   2        |   Jack    |    Driver
   3        |   Charlie |    Swimmer
   4        |   Negin   |    Engineer
   5        |   Zahara  |    Manager

In want to use the LISTAGG function to produce this result:

  CUST_NUMBER | NAME_PROFESSION
  -----------------------------
       1      | [Mike:Teacher,Engineer]
       2      | [Jack:Driver]
       3      | [Charlie:Swimmer]
       4      | [Negin:Engineer]
       5      | [Zahara:Manager]

What I've written so far is the query below:

select cust_number,
       '['||listagg(cust_name||':'||cust_profession,',')  within group (order by cust_number)||']' Cust_profession 
from CUSTOMER_PROFESSION 
group by cust_number;

The problem I have is that this gives me [Mike:Engineer,Mike:Teacher] (as you can see , the name Mike is repeated) instead of [Mike:Teacher,Engineer] . How can I solve this problem?

Thanks in advance

  • 1
    Extended answer added. – Akina Aug 11 at 11:35
2

You want to see employee name out of aggregated professions list - so you must concatenate it to aggregating expression, not to separate profession in arguments.

If do this directly

select cust_number,
       '['||MAX(cust_name)||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession 
from CUSTOMER_PROFESSION 
group by cust_number;

then cust_name become non-grouped, and Oracle produces an error "ORA-00979: not a GROUP BY expression".

This can be solved either by wrapping this column with aggregated function, for example, MAX():

select cust_number,
       '['||MAX(cust_name)||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession 
from CUSTOMER_PROFESSION 
group by cust_number;

or by adding this column into grouping expression (preferred):

select cust_number,
       '['||cust_name||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession 
from CUSTOMER_PROFESSION 
group by cust_number, cust_name;

fiddle

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.