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

user_2769987 0
Viewed 234

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?

sql-server sql
4 Answers

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.

user_11440350

try the following:

select 
* from YourTable
where REPLACE(TRIM('X' FROM col1),'A',1)
+ REPLACE(TRIM('X' FROM col2),'A',1)
+ REPLACE(TRIM('X' FROM col3),'A',1)
+ REPLACE(TRIM('X' FROM col4),'A',1)
+ REPLACE(TRIM('X' FROM col5),'A',1)
+ REPLACE(TRIM('X' FROM col6),'A',1)
+ REPLACE(TRIM('X' FROM col7),'A',1)
+ REPLACE(TRIM('X' FROM col8),'A',1)
+ REPLACE(TRIM('X' FROM col9),'A',1)> 1

I have used TRIM also which requires SQL Server 2017 or above.

or the following:

select *
from YourTable
where replace(replace(concat(col1,col2,col3,col4,col5,col6,col7,col8,col9), 'X', ''), 'A', 1) > 1

Please see the demo here.

user_39877840

Here is one way to do this

  create table dbo.t1(x int, col1 varchar(10), col2 varchar(10))


  insert into dbo.t1 values(1,'A','A')
  insert into dbo.t1 values(2,'A','')
  insert into dbo.t1 values(3,'','')
  insert into dbo.t1 values(4,'','X')





  select x
         ,count(case when n='A' then 1 end) as cnt      
    from dbo.t1
cross apply(values ('col1',col1)
                  ,('col2',col2)
                  ---repeat this for columns up to col9...
           )x(m,n)
  group by x
  having count(case when n='A' then 1 end)>1
user_71370900 
SELECT *
FROM   MyTable
WHERE  LEN(CONCATENATE(C.First, C.... , C.Eighth)) - LEN(REPLACE(CONCATENATE(C.First, C.... , C.Eighth), 'A', '')) = 1

As an example

user_126598720
Related