Search for duplicate values ​​in different columns in one row

Question

Before someone starts saying that this table needs to be normalized, best practices, etc. I agree that this is an old table that we have in SQL Server 2008 R2, and I can do nothing about it. Having said that, this table has the following columns:

"PreparedBy", "PrelimApprovalBy", "Approval1Signer", "Approval2Signer" 

All of these fields have either usernames, either NULL or ``. I want to get all the lines in which the same username appears in 2 OR MORE fields mentioned above. If 2 fields are NULL, they correspond to NOT , and they correspond to NOT if they are both characters. '' Therefore, both NULL and “need” are excluded, because they mean nothing.

HERE THAT I THINK SO FURTHER BUT DO NOT LIKE:
I mean checking all permutations in the WHERE clause (checking for NULL and ") by doing something line by line

 WHERE PreparedBy = PrelimApprovalBy OR PreparedBy = Approval1Signer OR ... 

There must be a better way to do this.

Answer 1

Here is one:

 SELECT * FROM T WHERE EXISTS (SELECT 1 FROM (VALUES (PreparedBy) ,(PrelimApprovalBy) ,(Approval1Signer) ,(Approval2Signer)) AS X (n) WHERE NULLIF(n, '') IS NOT NULL GROUP BY n HAVING COUNT(*)>1 ) 

Basically, for each row we build a mini-table with column values ​​in different rows and do GROUP BY and HAVING to check groups of matching values. NULLIF helps us ignore "values" (by making them NULL and then excluding all NULL).

Answer 2

Try this query:

 SELECT PreparedBy, PrelimApprovalBy, Approval1Signer, Approval2Signer WHERE ((PreparedBy = PrelimApprovalBy AND NULLIF(PreparedBy, '') IS NOT NULL) OR (PreparedBy = Approval1Signer AND NULLIF(PreparedBy, '') IS NOT NULL) OR (PreparedBy = Approval2Signer AND NULLIF(PreparedBy, '') IS NOT NULL) OR (PrelimApprovalBy = Approval1Signer AND NULLIF(PrelimApprovalBy, '') IS NOT NULL) OR (PrelimApprovalBy = Approval2Signer AND NULLIF(PrelimApprovalBy, '') IS NOT NULL) OR (Approval1Signer = Approval2Signer AND NULLIF(Approval1Signer, '') IS NOT NULL)) 

I cannot think of anything simpler to achieve what you are looking for.