1

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
APC
  • 144,005
  • 19
  • 170
  • 281
Milton
  • 171
  • 1
  • 4
  • 15

2 Answers2

5

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.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • Once I was doing above I got an Error, which says there is a limit you can go for. It is about 4K length. So, make sure you wont exceed your total character length. to_clob conversion also has limits. ORA-01489: result of string concatenation is too long 01489. 00000 - "result of string concatenation is too long" *Cause: String concatenation result is more than the maximum size. *Action: Make sure that the result is less than the maximum size. – Chand Priyankara Aug 06 '13 at 15:05
-1

Try this,

select customer
     , (select Product from yourtablename where pirority = 1) AS Main_Product
     , (select wm_concat(Product) from yourTablename where pirority = 2 ) AS Sub_product      
  from yourtablename 
 group by customer
Ben
  • 51,770
  • 36
  • 127
  • 149
Banana
  • 136
  • 1
  • 1
  • 7
  • 1
    Why would you use an undocumented function WM_CONCAT() when LISTAGG() exists in 11g (which the OP is using)? You're also accessing a table 3 times here... As your first access is only selecting a single row per customer you can do this: http://www.sqlfiddle.com/#!4/52137/4 instead, which removes one of the scans. – Ben Jul 06 '13 at 10:01
  • Ben i tested. Its working when the datatype as varchar2 not working with the datatype nvarchar2 – Milton Jul 06 '13 at 10:19
  • What does that mean @Milton? You tested the answer? It'll definitely work... Or did you test something else? – Ben Jul 06 '13 at 10:26