'How would I create one data set from a table that contains two identical values in the first column but different data in the remaining columns Oracle

I have produced a dataset where some of the data has two identical memberkeys but different contract values, while other memberkeys only appear once. I need to merge those memberkeys that have two rows into one distinct memberkey row containing all the data from both rows while leaving the single row memberkey as is.

Current

MemberKey SubscriberKey VALUEONE VALUETWO VALUETHREE VALUEFOUR VALUEFIVE VALUESIX VALUESEVEN VALUEEIGHT VALUENINE VALUETEN VALUEELEVEN
2235 H4931 MA84100303 ENGLISH ACOC 5TX4VV3TD79 13 1
2235 2235 A84100303 b ENGLISH AUCOC A84100303
4375 H4931 MA48450239 SPANISH APIM 9QP3K96WK88 14 1
4375 4375 A48450239 SPANISH AUPIM A48450239
375 375 H4931 MA08111511 ENGLISH AMAR 8B06P95CG54

Desired

MemberKey SubscriberKey VALUEONE VALUETWO VALUETHREE VALUEFOUR VALUEFIVE VALUESIX VALUESEVEN VALUEEIGHT VALUENINE VALUETEN VALUEELEVEN
2235 2235 A84100303 b H4931 MA84100303 ENGLISH ACOC AUCOC A84100303 5TX4VV3TD79 13 1
4375 4375 A48450239 H4931 MA48450239 SPANISH APIM AUPIM A48450239 9QP3K96WK88 14 1
375 375 H4931 MA08111511 ENGLISH AMAR 8B06P95CG54

I've tried several approaches (ctes, temp tables, convoluted joins etc) without success. Thanks

sqloracle


Solution 1:[1]

Could you create a table and then update this new table with your old table using by joining on the memberkey?

update new_table a
    set a.valueone = (select max(x.valueone)
                        from old_table x
                        where a.memberkey = x.memberkey);
commit;

This would work as long as for the same memberkey you do not have a different value for the same column.

Solution 2:[2]

This seems to work

MERGE INTO MEMBERS m2 USING MEMBERS_GTT m1 ON ( m1.MemberKey = m2.MemberKey ) WHEN MATCHED THEN UPDATE SET m2.SUBSCRIBERKEY = COALESCE(m1.SUBSCRIBERKEY,m2.SUBSCRIBERKEY)

WHEN NOT MATCHED 
 THEN 
  INSERT  ( MemberKey , 
 SUBSCRIBERKEY 
  ) 
   VALUES (  m1.MemberKey , 
       m1.SUBSCRIBERKEY 
   )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MatBailie
Solution 2 Robert L. Gaskin

Related Questions