Remove duplicate or repeating value in another row

Question

I have the following data in table test2

What I would like to achieve is to retrieve one row by removing duplicate values in any columns of the table, which means eliminate repeating or duplicate value of another row and result in one single row.

Database version is Oracle 10g.

The expected output is

Table structure and data

CREATE TABLE test2
(
   supplier_id        VARCHAR2 (256),
   supplier_name      VARCHAR2 (256),
   supplier_country   VARCHAR2 (256),
   contact_name       VARCHAR2 (256),
   address            VARCHAR2 (256)
);


SET DEFINE OFF;
Insert into TEST2
   (SUPPLIER_ID, SUPPLIER_NAME, SUPPLIER_COUNTRY, CONTACT_NAME, ADDRESS)
 Values
   ('IS230856', 'XYZ Inc.', 'U.S.', 'Jones', 'P.O. Box 2354');
Insert into TEST2
   (SUPPLIER_ID, SUPPLIER_NAME, SUPPLIER_COUNTRY, CONTACT_NAME, ADDRESS)
 Values
   ('IS230856', 'XYZ Inc.', 'U.S.', 'James', 'P.O. Box 2358');
Insert into TEST2
   (SUPPLIER_ID, SUPPLIER_NAME, SUPPLIER_COUNTRY, CONTACT_NAME, ADDRESS)
 Values
   ('IS230856', 'XYZ Inc.', 'U.S.', 'James', 'P.O. Box 2354');
Insert into TEST2
   (SUPPLIER_ID, SUPPLIER_NAME, SUPPLIER_COUNTRY, CONTACT_NAME, ADDRESS)
 Values
   ('IS230856', 'XYZ Inc.', 'U.S.', 'Jones', 'P.O. Box 2358');
Insert into TEST2
   (SUPPLIER_ID, SUPPLIER_NAME, SUPPLIER_COUNTRY, CONTACT_NAME, ADDRESS)
 Values
   ('IS230856', 'XYZ Inc.', 'U.S.', 'Smith', 'P.O. Box 2354');
COMMIT;

What have you tried? there are some techniques explained in questions like http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle — Florin Ghita, Mar 08 '16 at 12:39

MT0 · Accepted Answer · 2016-03-08T12:51:27.173

1

Oracle Setup:

CREATE TYPE VARCHAR2s_Table AS TABLE OF VARCHAR2(4000);

CREATE FUNCTION to_String(
  p_strings VARCHAR2s_Table,
  p_delim   VARCHAR2        DEFAULT ','
)
RETURN VARCHAR2
IS
  o_str VARCHAR2(4000);
BEGIN
  IF p_strings IS NULL OR p_strings IS EMPTY THEN
    RETURN NULL;
  END IF;
  o_str := p_strings(1);
  FOR i IN 2 .. p_strings.COUNT LOOP
    o_str := o_str || p_delim || p_strings(i);
  END LOOP;
  RETURN o_str;
END;
/

Query:

SELECT supplier_id,
       supplier_name,
       supplier_county,
       TO_STRING( SET( CAST( COLLECT( contact_name ) AS VARCHAR2s_Table ) ) )
         AS contact_names,
       TO_STRING( SET( CAST( COLLECT( address ) AS VARCHAR2s_Table ) ) )
         AS addresses
FROM   test2
GROUP BY supplier_id,
       supplier_name,
       supplier_county;

edited Mar 08 '16 at 12:51

answered Mar 08 '16 at 12:31

MT0

143,790
11
59
117

Contact_Names and Address values are not distinct, E.g. `Jones` and `James` are repeating. – Jacob Mar 08 '16 at 12:34
1

I've only got 11gR2 to test this on and, in that, the `DISTINCT` keyword removes the duplicates from each collection... You can try wrapping each `CAST(...)` statement in a `SET()` operation like this: `TO_STRING( SET( CAST( COLLECT( contact_name ) AS VARCHAR2s_Table ) ) )` – MT0 Mar 08 '16 at 12:38
1

The [Oracle 10 documentation](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions024.htm) makes no reference to `DISTINCT` but it appears in the [11.1 Documentation](https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions024.htm) so it looks like that simple option is out. The `SET()` collection operator appears in 10g guides [[1](http://www.oracle.com/technetwork/issue-archive/o53plsql-083350.html), [2](https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm)] so you should be able to use it at that version but I can't find where it is documented... – MT0 Mar 08 '16 at 12:48
I have been struggling to achieve the desired output, however, all in vain. With your solution I am able to get the desired output. Thanks and much appreciated. – Jacob Mar 08 '16 at 13:06
I have a question, is it possible to out repeating values in a new row instead of separating it using a comma? – Jacob Apr 10 '16 at 11:17

score 0 · Answer 2 · answered Mar 08 '16 at 11:48

0

You can use ORACLE's listagg :

SELECT t.supplier_ID,t.supplier_name,t.supplier_country,
       listagg(t.contact_name,',') within group(order by t.contact_name),
       listagg(t.address,',') within group(order by t.address)
FROM TEST2 t
GROUP BY  t.supplier_ID,t.supplier_name,t.supplier_country

answered Mar 08 '16 at 11:48

sagi

40,026
6
59
84

2

Does listagg support Oracle 10g? if I am not mistaken it supports only from 11g R2 and above. – Jacob Mar 08 '16 at 11:49

Remove duplicate or repeating value in another row

2 Answers2