CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Jacob
Jacob

Reputation: 14731

Remove duplicate or repeating value in another row

I have the following data in table test2

enter image description here

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

enter image description here

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;

Upvotes: 0

Views: 61

Answers (2)

MT0
MT0

Reputation: 167774

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;

Upvotes: 1

sagi
sagi

Reputation: 40471

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

Upvotes: 0

Related Questions