1
A   B   C    Equal
1   1   1    Y
2   2   NULL N

Hi, I have 3 columns which I need to compare : Columns A,B,C. If these 3 columns are the same then column 'Equal' = 'Y'

But, if a column is null, then it must be excluded from the comparison, e.g. in the example Row 2 has a null, but the remaining columns are all the same, so the Equal should be yes - because all non-null values are equal

http://www.sqlfiddle.com/#!3/dedd9/2

I thought I could use isnull(C,A) = A but it does not work. Could anyone help please

 declare @t table
 (
 A decimal,B decimal, C decimal
 )

 insert Into @t(A,B,C) values (1,1,1)
 insert Into @t(A,B) values (2,2)

 select A,B,C,'Equal' = case when ISNULL(A,B) = B and ISNULL(B,C) = C and ISNULL(C,A) = A then 'Y' else '' end
 from @t
2

Another way, that can be easily extended to more than 3 columns:

SELECT a, b, c,
       Equal = ( SELECT r = CASE WHEN MIN(v) = MAX(v) THEN 'Y'  -- all non-null values equal
                                 WHEN MIN(v) < MAX(v) THEN 'N'  -- at least 2 non equal 
                                 ELSE 'Y'                       -- all nulls
                            END 
                 FROM (VALUES (a), (b), (c)) AS x (v)
               ) 
FROM t ;

Tested at SQL-Fiddle.

3

Assuming you want all NULLs to count as equal, I think this will work. But I haven't thoroughly tested it, or even thought about it for very long...

 select A,B,C,'Equal' = 
    case when COALESCE(A,B,C,0) = COALESCE(B,C,A,0)
          and COALESCE(B,C,A,0) = COALESCE(C,A,B,0)
     then 'Y' else '' end
 from @t

COALESCE will pick the first non-NULL value from its arguments, so when testing A against B and then B against C, in each case we look 'forward' for a non-NULL value, with a fallback for when they're all NULL

1

You were almost there with your query. However, your query fails, when validating isnull(A,B) = B, when B is null, since NULL does not equal NULL in sql.

try this:

 declare @t table
 (
 A decimal,B decimal, C decimal
 )

 insert Into @t(A,B,C) values (1,1,1)
 insert Into @t(A,B) values (2,2)
 insert Into @t(A,C) values (2,2)
 insert Into @t(A,C) values (2,3)
 insert Into @t(A,B,C) values (2,2,3)


select 
  A
, B
, C
, 'Equal' = case when ISNULL(A,B) = ISNULL(B, A)
                  and ISNULL(B,C) = ISNULL(C, B)
                  and ISNULL(C,A) = ISNULL(A, C)
            then 'Y' else '' end
 from @t

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.