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)?
CodePudding user response:
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