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.
Advertisement
Answer
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.