Fill columns which have priority over other columns in SQL Server

Question

I have a table like this:

id	mail_1	mail_2	mail_3
1	john	john_v2	john_v3
2	clarisse	NULL	clarisse_company
3	NULL	julie	NULL
4	mark	markus_91	NULL
5	alfred	NULL	NULL

And I would like to achieve that:

id	mail_1	mail_2	mail_3
1	john	john_v2	john_v3
2	clarisse	clarisse_company	NULL
3	julie	NULL	NULL
4	mark	markus_91	NULL
5	alfred	NULL	NULL

As you can see, if mail_2 or mail_3 are not null and mail_1 is null, mail_1 should be fulfilled. The thing here is if the id has two mails, this two mails must be in mail_1 and mail_2, not in mail_2 and mail_3 nor mail_1 and mail_3. If an id has just one mail, this mail must be in mail_1.

So the logic here is that mail_1 has priority over the other two, and mail_2 has priority over mail_3.

How could I achieve that in SQL Server (version 15)?

Answer 1

This should do. Just play by changing the values of the table variable below

declare @temp table(mail_1 varchar(20),mail_2 varchar(20),mail_3 varchar(20))

insert into @temp values(null,'middlename','lastname')

select coalesce(mail_1,mail_2,mail_3) as mail_1,
case when mail_1 
is null and mail_2 is not null then mail_3
when mail_1 
is not null and mail_2 is  null 
 then
mail_3 
else mail_2 end mail_2,
case when (mail_1 is null or mail_2 is null) then null else mail_3 end mail_3
from @temp