Matching 3 columns in row - if one column is null then exclude it from comparison 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
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