Assign NULL to repeated values in different columns in SQL-CodePudding

I have a table like this:

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

And I would like to put NULLs where the mail is repeated, for example in the row 2 mail_1 and mail_2 have the same value; clarisse, and I would like to assign a NULL in mail_2. So I'm thinking in an algorithm that first fix the row and then go through the columns and check if the current value is the same as the previous. So the final table would be something like this:

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

Doing this with other languages is pretty easy, such Python or R, but I would like to have it done in SQL.

Any ideas? Thanks.

CodePudding user response：

Seems you could use NULLIF:

SELECT id,
       Mail1,
       NULLIF(Mail2,Mail1) AS Mail2,
       NULLIF(NULLIF(Mail3,Mail2),Mail1) AS Mail3
FROM dbo.YourTable;

Ideally, however, you should be normalising your design. Don't have 3 mail columns, have just one. If a "thing" can have multiple values for Mail, then you need 2 tables with a one to many relationship. If 2 "things" can have the same Mail as well, then you need 3 tables, so that you can define a many to many relationship.

CodePudding user response：

use case when

select id,mail_1,
case when mail_1=mail2 then null else mail_2 end as mail_2, mail_3
from table_name