How would I create one data set from a table that contains two identical values in the first column-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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 
   )