I am not sure how to get the right script

usually, I use listagg by order by but I want something different

select
customer_id ,
listagg (car_product , '-' ) within group (order by car_product) as Product
from purchase
group by customer_id

I want output results like keep first ZINC then the rest are listagg, here is an example;

yep 939 - doesn't have ZINC so just pick other in there

thank you

CodePudding user response：

Fix order by clause so that it sorts ZINC first, and then the rest.

Sample data:

SQL> with purchase (customer_id, car_product) as
  2    (select 123, 'ZINC'  from dual union all
  3     select 123, 'BLUE'  from dual union all
  4     select 123, 'RED'   from dual union all
  5     --
  6     select 939, 'BLACK' from dual union all
  7     select 939, 'BLUE'  from dual
  8    )

Query:

  9  select customer_id,
 10    listagg(car_product, '-') within group
 11      (order by case when car_product = 'ZINC' then 'A' else car_product end) as product
 12  from purchase
 13  group by customer_id;

CUSTOMER_ID PRODUCT
----------- --------------------
        123 ZINC-BLUE-RED
        939 BLACK-BLUE

SQL>