SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select *
into customer
from (values (111, 'DEW', null, 1, null),	
             (112, 'ERU', 2, null,  2)
     ) v(CustomerId, Name, sign_up_Referral_By, Download_Referral_By, Order_Referral_By)

             
2 rows affected

select *
into partner
from (values (1, 'abc'),
             (2, 'xyz')
) v(partnerid, name)
2 rows affected

select v.partnerid, p.name, 
       sum(is_signup), sum(is_download), sum(is_order)
from customer c cross apply
     (values (Sign_up_Referral_By, 1, 0, 0),
             (Download_Referral_By, 0, 1, 0),
             (Order_Referral_By, 0, 0, 1)
     ) v(partnerid, is_signup, is_download, is_order) join
     partner p
     on v.partnerid = p.partnerid
group by v.partnerid, p.name;


partnerid
name
(No column name)
(No column name)
(No column name)


1
abc
0
1
0

2
xyz
1
0
1