While waiting for what you tried I thought I'd give it a shot. Is this basically what you were looking for?

select

*,

rn = row_number() over (partition by Cust_Number order by

case when Has_Account = 'Yes' then 1

when CUSTein > '' then 2

when Has_Cert = 'Yes' then 3

else 4

end, CustSGID)-- as SortOrder

from

[dbo].[Duplicate_Cust_Numbers_to_DeDupe];

with dedup_id as (

select

*,

rn = row_number() over (partition by Cust_Number order by

case when Has_Account = 'Yes' then 1

when CUSTein > '' then 2

when Has_Cert = 'Yes' then 3

else 4

end, CustSGID)-- as SortOrder

from

[dbo].[Duplicate_Cust_Numbers_to_DeDupe]

)

select

Cust_Number,

max(case rn when 1 then Cust_Name else '' end) as Cust_Name,

max(Has_Account) as Has_Account,

max(Has_Cert) as Has_Cert,

max(case rn when 1 then CustSGID else '' end) as PrimaryCustSGID,

max(case rn when 2 then CustSGID else '' end) as SecondaryCustSGID

from

dedup_id

where

rn <= 2 -- Is it possible there may be more than 2 duplicate Cust_Number records?

group by

Cust_Number