oracle - identify and handle duplicate records via sql -

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 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 orderdetail values ('or12345','5025','metalic clips','1234567890','red',500); insert orderdetail values ('or12345','5025','metalic clips','1234567890','blue',500); insert orderdetail values ('or12345','5025','metalic clips','7890123456','green',1000); insert orderdetail values ('or12345','5025','metalic clips','6789012345','yellow',1000); insert orderdetail values ('or12345','5025','metalic clips','5678901234','orange',1000); insert orderdetail values ('or12345','5025','metalic clips','4567890123','pink',1000); insert orderdetail values ('or12345','5025','metalic clips','3456789012','brown',1000); insert orderdetail values ('or12345','5025','metalic clips','2345678901','grey',2500); insert 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 duplicate barcode concatenate color , sum qty ….can achieved sql query in oracle...any appreciated

if you're using 11gr2 can use listagg:

select oi.ordernum, supplier, productdesc, sum(od.qty) qty, listagg(color, '||') within group (order  oi.ordernum) color, barcode, eta, pol, pod orderinfo oi join orderdetail od on oi.ordernum = od.ordernum group oi.ordernum, supplier, pol, pod, etd, eta, productcode , productdesc, barcode  

here sqlfiddle demo

---

since you're not using 11gr2 can either use 1 of options in alex poole's link, or (with xmlagg):

select oi.ordernum, supplier, productdesc, sum(od.qty) qty,  rtrim(xmlagg(xmlelement(e,color || '||')).extract('//text()'), '||') color, barcode, eta, pol, pod orderinfo oi join orderdetail od on oi.ordernum = od.ordernum group oi.ordernum, supplier, pol, pod, etd, eta, productcode , productdesc, barcode ; 

here sqlfiddle demo