identify and handle duplicate records via sql

Question

Create table OrderInfo ( ordernum VARCHAR2(17), supplier VARCHAR2(17), pol VARCHAR2(17), pod VARCHAR2(17), etd DATE, eta DATE, productcode VARCHAR2(17), qty Number(10) ) / Create table orderdetail ( ordernum VARCHAR2(17), product_code VARCHAR2(17), productdesc VARCHAR2(50), barcode VARCHAR2(17), color VARCHAR2(17), qty number(10) ) / Insert into orderinfo values ('OR12345','TATA','MUMBAI','CAIRO',To_Date('21/06/2013','dd/mm/yyyy'), To_Date('27/07/2013','dd/mm/yyyy'),'5025',10000 ); Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','1234567890','RED',500); Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','1234567890','BLUE',500); Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','7890123456','GREEN',1000); Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','6789012345','YELLOW',1000); Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','5678901234','ORANGE',1000); Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','4567890123','PINK',1000); Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','3456789012','BROWN',1000); Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','2345678901','GREY',2500); Insert into orderdetail values ('OR12345','5025','METALIC CLIPS','2345678901','SILVER',1500); COMMIT / Output Required Ordernum supplier productdesc barcode color qty etd eta pol pod ------------------------------------------------------------------------------------------------------------------------------------- OR12345 TATA METALIC CLIPS 1234567890 RED || BLUE 1000 21/06/2013 27/07/2013 MUMBAI CAIRO OR12345 TATA METALIC CLIPS 7890123456 GREEN 1000 21/06/2013 27/07/2013 MUMBAI CAIRO OR12345 TATA METALIC CLIPS 6789012345 YELLOW 1000 21/06/2013 27/07/2013 MUMBAI CAIRO OR12345 TATA METALIC CLIPS 5678901234 ORANGE 1000 21/06/2013 27/07/2013 MUMBAI CAIRO OR12345 TATA METALIC CLIPS 4567890123 PINK 1000 21/06/2013 27/07/2013 MUMBAI CAIRO OR12345 TATA METALIC CLIPS 3456789012 BROWN 1000 21/06/2013 27/07/2013 MUMBAI CAIRO OR12345 TATA METALIC CLIPS 2345678901 GREY || SILVER 4000 21/06/2013 27/07/2013 MUMBAI CAIRO

+++++++++++++++++++++++++++++++

If there is a duplicate barcode then concatenate the color and sum up the qty ….can this be achieved by sql query in oracle...any help would be appreciated

A.B.Cade · Accepted Answer · 2013-07-24 10:09:02Z

If you're using 11gR2 you can use listagg:

select oi.ORDERNUM, SUPPLIER, PRODUCTDESC, sum(od.qty) qty, listagg(color, '||') within group (order by oi.ORDERNUM) color, BARCODE, ETA, POL, POD from OrderInfo oi join orderdetail od on oi.ordernum = od.ordernum group by oi.ORDERNUM, SUPPLIER, POL, POD, ETD, ETA, PRODUCTCODE , PRODUCTDESC, BARCODE

Here is a sqlfiddle demo

Since you're not using 11gR2 you can either use one of the options in Alex Poole's link, or do it like this (with xmlagg):

select oi.ORDERNUM, SUPPLIER, PRODUCTDESC, sum(od.qty) qty, rtrim(xmlagg(xmlelement(e,color || '||')).extract('//text()'), '||') color, BARCODE, ETA, POL, POD from OrderInfo oi join orderdetail od on oi.ordernum = od.ordernum group by oi.ORDERNUM, SUPPLIER, POL, POD, ETD, ETA, PRODUCTCODE , PRODUCTDESC, BARCODE ;

Here is another sqlfiddle demo

And if you aren't on 11gR2 there are some well-known string aggregation techniques.
Sorry this doesn't work for me ...I am on "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod"

bvr · Accepted Answer · 2013-07-24 09:06:40Z

Try this

SELECT oi.Ordernum ,supplier ,productdesc ,barcode ,listagg(color, ' || ') within group (order by color) as color ,SUM(od.qty) AS qty ,etd ,eta ,pol ,pod FROM orderinfo oi INNER JOIN orderdetail od ON oi.ordernum = od.ordernum GROUP BY oi.Ordernum,supplier,productdesc,barcode,etd,eta,pol,pod

SQL FIDDLE DEMO

Sorry this doesn't work for me ...I am on "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod"
Anyone who can help the listagg doesn't work for me as I am on 10g

Collectives™ on Stack Overflow

identify and handle duplicate records via sql

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related