How can I identify the rows in this sample, where only one of the columns is "Unknown"?

• If both columns are the same, but not unknown, that's great - use either
• If one is unknown and the other column is not, that's also great - use the other.
• If they are both unknown, that's a problem easily managed

Putting a heap of nested if then type clauses doesn't sound like the smartest way to do it.

Using SQL Server 11x

 CASE 
        WHEN [Test1]  = 'Unknown' AND [Test2] <> 'Unknown' THEN [Test2] 
        WHEN [Test1] <> 'Unknown' AND [Test2] =  'Unknown' THEN [Test1] 
        WHEN [Test1] <> 'Unknown' AND [Test2] <> 'Unknown' AND [Test1] <> [Test2] THEN 'Issue' 

        ELSE 'xxxx'
        END AS test

CodePudding user response：

You could use:

SELECT ROW, ID, Name,
       CASE WHEN Test1 = 'Unknown' THEN Test2 ELSE Test1 END AS Test
FROM yourTable;

This will report in Test the value of Test1 if not unknown, otherwise the value of Test2. In the case of both being unknown, it would report unknown.