I’m going to assume that the PRIORITY column is always 1 when there’s a “main product” and never 1 any other time. From your data it also looks like each customer has only one “main” product. I’m going to assume that this is true. If it’s not then you should have another column to distinguish product groups. You can simply add this into the below.
The complicated/efficient answer may be as follows:
select customer
, max(product) keep (dense_rank first order by priority) as main_product
, listagg(case when priority = 2 then product end, ', ')
within group (order by product) as sub_product
from products
group by customer
Per customer, the PRODUCT column assumes that every customer has a main product, then gets the first product in order by priority. The second column only takes where the priority is 2 and uses the string concatenation function LISTAGG() to concatenate your values together.
I would highly recommend Rob van Wijk’s blog post about the KEEP clause.
A more standard SQL solution would look like this:
select a.customer, a.product as main_product
, listagg(b.product, ', ') within group (order by b.product) as sub_product
from products a
join products b
on a.customer = b.customer
where a.priority = 1
and b.priority = 2
group by a.customer, a.product
i.e. find everything that has a priority of 1, use this to generate your two rows and then get everything with a priority of 2 and aggregate those.