user641887 user641887 - 6 months ago 10
SQL Question

SQL how to merge records Oracle

I have a table which is something like the below

Key CL EmailAddress CT Product1 Product2 Product3 Product4 Product5
1 X abc@gmail.com A 12 null null null null
2 X abc@gmail.com B 123 22 null null null


For each row I can have a maximum of 5 products. Each record would have a minimum of 1 but less than 5 products.

Also the email address can repeat across the same CL.

I would have to write a query to find if the email address repeats across the same CL and if it does

I would have to merge the productids for the same email address.

On doing the merge if I have got 5 products I need to stop and exclude the remaining products.

So the output for the above example should look something like this

Key CL EmailAddress CT Product1 Product2 Product3 Product4 Product5
1 X abc@ gmail.com A+B 12 123 22 null null


Can we do something like this in Oracle SQL query?

MT0 MT0
Answer

Oracle Setup

CREATE TABLE table_name (
  "Key"        INT PRIMARY KEY,
  CL           CHAR(1),
  EmailAddress VARCHAR2(100),
  CT           VARCHAR2(100),
  Product1     INT,
  Product2     INT,
  Product3     INT,
  Product4     INT,
  Product5     INT
);

INSERT INTO table_name
SELECT 1, 'X', 'abc@gmail.com', 'A', 12,  null, null, null, null FROM DUAL
UNION ALL
SELECT 2, 'X', 'abc@gmail.com', 'B', 123,   22, null, null, null FROM DUAL;

CREATE TYPE stringlist AS TABLE OF VARCHAR2(100);
/

CREATE OR REPLACE FUNCTION nth_item(
  collection STRINGLIST,
  n          INT
) RETURN VARCHAR2 DETERMINISTIC
AS
BEGIN
  IF collection IS NULL OR n < 1 OR n > collection.COUNT THEN
    RETURN NULL;
  END IF;
  RETURN collection(n);
END;
/

Query:

SELECT "Key",
       CL,
       EmailAddress,
       CT,
       Nth_Item( products, 1 ) AS Product1,
       Nth_Item( products, 2 ) AS Product2,
       Nth_Item( products, 3 ) AS Product3,
       Nth_Item( products, 4 ) AS Product4,
       Nth_Item( products, 5 ) AS Product5
FROM   (
  SELECT MIN( "Key" ) AS "Key",
         CL,
         EmailAddress,
         REGEXP_REPLACE(
           LISTAGG( CT, '+' ) WITHIN GROUP ( ORDER BY CT ),
           '(.)(\+\1)+',
           '\1'
         ) AS CT,
         CAST( COLLECT( COLUMN_VALUE ) AS stringlist ) AS products
  FROM   table_name t,
         TABLE(
           STRINGLIST(
             t.Product1,
             t.Product2,
             t.Product3,
             t.Product4,
             t.Product5
           )
         )
  WHERE  COLUMN_VALUE IS NOT NULL
  GROUP BY CL, EmailAddress
);

Output:

Key CL EMAILADDRESS  CT  PRODUCT1 PRODUCT2 PRODUCT3 PRODUCT4 PRODUCT5
--- -- ------------- --- -------- -------- -------- -------- --------
  1 X  abc@gmail.com A+B       12       22      123