Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate data in comma seperated output (Database version - Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod)
Duplicate data in comma seperated output [message #327454] Mon, 16 June 2008 08:12 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Database version - Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

I have a requirement to find the duplicate and present the result in comma seperated output. The data is in following format

create table test
(
package  varchar2(100),
filename varchar2(100),
version  varchar2(100),
source_package  varchar2(100)
);


insert into test
values ('P1','File1.sql','1',NULL);

insert into test
values ('P2','File1.sql','2',NULL);

insert into test
values ('P3','File1.sql','3',NULL);

insert into test
values ('P10','File1.sql','3','P3');


insert into test
values ('P21','File2.sql','1',NULL);

insert into test
values ('P22','File2.sql','2',NULL);

insert into test
values ('P25','File2.sql','3','P22');


insert into test
values ('P51','File3.sql','1',NULL);

insert into test
values ('P55','File3.sql','1','P51');


Package		Filename	Version		Source package Duplicate package
--------------------------------------------------------------------
P10		File1.sql	3		P3              P1,P2,P3
P25		File2.sql	3		P22             P21,P22
P55		File3.sql	1		P51

I tried this, but not sure how to get the comma seperated output...

select p1.package, p1.filename, p1.version , p2.package
from test p1, test p2
where p1.source_package = p2.package;

PACKAGE|FILENAME |VERSION|PACKAGE_1
P10    |File1.sql|3      |P3
P25    |File2.sql|3      |P22
P55    |File3.sql|1      |P51

	

Re: Duplicate data in comma seperated output [message #327461 is a reply to message #327454] Mon, 16 June 2008 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First thanks for posting the test case.

Your requirements are not clear, what is a duplicate package? How your "Duplicate package" is built? From which rules?

In the end, getting it in a comma separated string is easy with concat_all, stragg or the like functions.

Regards
Michel
Re: Duplicate data in comma seperated output [message #327489 is a reply to message #327454] Mon, 16 June 2008 10:11 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Quote:
First thanks for posting the test case.
Smile It is my requirement, so I have to provide it.

Duplicate package is for the file name, wherever the source_package has value, for that filename, we need to see if there are any duplicates records apart from the source package. Like, for P10 (File1.sql), the source is P3, but the File1.sql also has other records with prior versions. So I need to report them in the ouput, which has filename other than the source package.

I hope I clear it now...
Re: Duplicate data in comma seperated output [message #327499 is a reply to message #327489] Mon, 16 June 2008 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col package format a7
SQL> col filename format a9
SQL> col version format a1
SQL> col source_package format a6
SQL> col duplicate_package format a17
SQL> select * from test order by filename, version, source_package nulls first;
PACKAGE FILENAME  V SOURCE
------- --------- - ------
P1      File1.sql 1
P2      File1.sql 2
P3      File1.sql 3
P10     File1.sql 3 P3
P21     File2.sql 1
P22     File2.sql 2
P25     File2.sql 3 P22
P51     File3.sql 1
P55     File3.sql 1 P51

9 rows selected.

SQL> with
  2    data as (
  3      select package, filename, version, source_package,
  4             row_number () 
  5               over (partition by filename order by source_package nulls last, version desc)
  6               rn 
  7      from test
  8    ),
  9    duplicate as (
 10      select filename, wm_concat(package) duplicate_package
 11      from data
 12      where rn != 1
 13      group by filename
 14    )
 15  select t.package, t.filename, t.version, t.source_package, d.duplicate_package
 16  from data t, duplicate d
 17  where d.filename = t.filename
 18    and t.rn = 1
 19  order by 1
 20  /
PACKAGE FILENAME  V SOURCE DUPLICATE_PACKAGE
------- --------- - ------ -----------------
P10     File1.sql 3 P3     P3,P2,P1
P25     File2.sql 3 P22    P22,P21
P55     File3.sql 1 P51    P51

3 rows selected.

Execute it step by step and come back if you have some questions.

Regards
Michel
Re: Duplicate data in comma seperated output [message #327502 is a reply to message #327454] Mon, 16 June 2008 11:19 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Thanks Michel for the answer but I need only for duplicated packages..

PACKAGE FILENAME  V SOURCE DUPLICATE_PACKAGE
------- --------- - ------ -----------------
P10     File1.sql 3 P3     P3,P2,P1
P25     File2.sql 3 P22    P22,P21
P55     File3.sql 1 P51    P51

Actually it should be:-

Package		Filename	Version		Source package Duplicate package
--------------------------------------------------------------------
P10		File1.sql	3		P3              P1,P2,P3
P25		File2.sql	3		P22             P21,P22
P55		File3.sql	1		P51


If you see the last record is missing the P51...
Re: Duplicate data in comma seperated output [message #327503 is a reply to message #327454] Mon, 16 June 2008 11:23 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

I did it other way (not sure if it is a clean way)

with
      data as (
        select package, filename, version, source_package,
               row_number () 
                 over (partition by filename order by source_package nulls last, version desc)
                 rn 
        from test
      ),
      duplicate as (
       select filename, wm_concat(package) duplicate_package
       from data
       where rn !=1
       group by filename
     )
   select t.package, t.filename, t.version, t.source_package, CASE WHEN INSTR(d.duplicate_package,',') =0 THEN NULL ELSE d.duplicate_package END CASE
   from data t, duplicate d
   where d.filename = t.filename
     and t.rn = 1
   order by 1
Re: Duplicate data in comma seperated output [message #327505 is a reply to message #327502] Mon, 16 June 2008 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But P51 is a duplicate of P55 or maybe I miss something in your requirement.
If P51 is not a duplicate of P55, why P3 is a duplicate of P10?

Regards
Michel
Re: Duplicate data in comma seperated output [message #327509 is a reply to message #327454] Mon, 16 June 2008 11:34 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Quote:
But P51 is a duplicate of P55 or maybe I miss something in your requirement.
If P51 is not a duplicate of P55, why P3 is a duplicate of P10?


As we don't need to list the package which are directly from source, but if there are more than 1 packages then we need to list it..

Anyways thanx a lot for your answer. Just wondering if wm_concat is supported by Oracle? Otherwise I may be looking at an option of using SYS_CONNECT_BY_PATH as well for this??
Re: Duplicate data in comma seperated output [message #327511 is a reply to message #327509] Mon, 16 June 2008 12:09 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Just wondering if wm_concat is supported by Oracle?

It is not documented but seems to be there for a while.
Otherwise search here for stragg or concat_all.

Regards
Michel
Previous Topic: SQL Query Help - Creating Report
Next Topic: Oracle Installation
Goto Forum:
  


Current Time: Mon Dec 05 10:58:11 CST 2016

Total time taken to generate the page: 0.28504 seconds