sql - Oracle string aggregation

Question

My table structure look like this, I am new for the this field. I know the basic queries.But it's complicated for me. Please help me to do this.

Table structure

  Customer          Product         piriority
    10001           Main_product    1
    10001           Sub_product1    2
    10001           Sub_product2    2
    10001           Sub_product3    2
    10001           Sub_product4    2
    10002           Main_product    1
    10002           Sub_product1    2
    10002           Sub_product2    2

Expected Output:

Customer        Main_Product    Sub_product
10001           Main_product    Sub_product1,Sub_product2,Sub_product3,Sub_product4
10002           Main_product    Sub_product1,Sub_product2

Answer 1

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

SQL Fiddle

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.