Merge records that have at least one non-null element in a specific column – SQL Server

My data looks like as following:

id      ProteinID   Gene_Name   Class
10008   P08648      ITGA5       extracellular
10009   P08648      ITGA5       extracellular
10011   P08473      MME 10      NULL
10011   P08473      MME 10      extracellular
10013   P12111      COL6A3      NULL
10016   P09619      PDGFRB      NULL
10016   P09619      PDGFRB      intracellular

​x
 
id      ProteinID   Gene_Name   Class10008   P08648      ITGA5       extracellular10009   P08648      ITGA5       extracellular10011   P08473      MME 10      NULL10011   P08473      MME 10      extracellular10013   P12111      COL6A3      NULL10016   P09619      PDGFRB      NULL10016   P09619      PDGFRB      intracellular​

I would like to merge records that have both NULL and a NON-NULL value in the [Class] column but at the same time to not affect rows that have only a NULL value (e.g. id 10013)

Following is the desired output:

id      ProteinID   Gene_Name   Class
10008   P08648      ITGA5       extracellular
10009   P08648      ITGA5       extracellular
10011   P08473      MME 10      extracellular
10013   P12111      COL6A3      NULL
10016   P09619      PDGFRB      intracellular

 
id      ProteinID   Gene_Name   Class10008   P08648      ITGA5       extracellular10009   P08648      ITGA5       extracellular10011   P08473      MME 10      extracellular10013   P12111      COL6A3      NULL10016   P09619      PDGFRB      intracellular​

I have tried using the COALESCE function but it expunges all the rows with NULL in [Class].

I’m sure there is a simple way of doing that but I’ve been stuck.

Thanks in advance.

Answer

Not sure if this is your intent

Example

Select top 1 with ties * 
 From  YourTable
 Order By Row_Number() over (Partition By ID,ProteinID,Gene_Name  order by Class Desc)

 
Select top 1 with ties *  From  YourTable Order By Row_Number() over (Partition By ID,ProteinID,Gene_Name  order by Class Desc)​

Returns

Advertisement

Answer