SQL determine if more than one column on a given row has the same value

I have a SQL Server table that includes 9 columns used to indicated what things to include or exclude in part of the UI. Each column can have the value ‘A’, ‘X’, or blank. Each row should have at most 1 ‘A’ in any of the columns.

Due to an error many columns have multiple ‘A’ values. How can I write a query that returns every row that breaks this constraint?

All I have is something like:

SELECT PrimaryKey
FROM Criteria C
WHERE (C.First = 'A' AND C.Second = 'A')
   OR (C.First = 'A' AND C.Third = 'A')
   OR (C.First = 'A' AND C.Fourth = 'A')
   ...
   OR (C.Eighth = 'A' AND C.Ninth = 'A')

Is there any cleaner or more elegant way to write this code?

Answer

You can use APPLY:

SELECT C.*
FROM Criteria C CROSS APPLY
     (SELECT COUNT(*) as num_a_s
      FROM (VALUES (First), (Second), . . .  -- list all the columns here
           ) V(x)
      WHERE v.x = 'A'
     ) v
WHERE v.num_a_s >= 2;

Note: Something is probably wrong with your data model if you are storing these values in columns rather than in separate rows.