Compare each column in a row and return an error if any of them is different

Question

I'm attempting to find any identifiers with any values that are different in the same row.

+------------+--------+--------+--------+--------+
| Identifier | Value1 | Value2 | Value3 | Value4 |
| f001       | a1     | a2     | a2     | a2     |
| f002       | a4     | a4     | a4     | a5     |
| f003       | a2     | a2     | a2     |        |
| f004       | a1     | a1     | a1     | a1     |
| f002       | a9     |        |        |        |
+------------+--------+--------+--------+--------+

For example,

The first identifier should return "MisMatch",
The second identifier should return "MisMatch",
The third identifier should return "NoIssue",
The fourth identifier should return "Mismatch",
The fifth identifier should return "NoIssue",

Any help would be great, I'm stuck at this point.

Some lines have hundreds of columns, while others only have one. I'm hoping to be able to locate any line that contains any mismatch.

I'm using SQL Server 2008 R2.

score 7 · Answer 1 · edited Apr 13 '17 at 12:43

I see this question is tagged both Excel and SQL Server.

In Excel you could use the approach from here

The formula in cell F2 above is

=IF(SUM(IF(FREQUENCY(IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""), IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""))>0,1))>1,"MisMatch","Match")

Pay attention to the note in the linked article

The formulas in this example must be entered as array formulas. Select each cell that contains a formula, press F2, and then press CTRL+SHIFT+ENTER.

Curly braces will then appear around the formula in the formula bar

Another (SQL Server) way (credit to Geoff Patterson for improvements) would be

SELECT T.Identifier,
       CASE
         WHEN min_val <> max_val
           THEN 'MisMatch'
         ELSE 'NoIssue'
       END
FROM   @T T
       CROSS APPLY (SELECT MIN(Val),
                      MAX(Val)
                    FROM   (VALUES (Value1),
                                   (Value2),
                                   (Value3),
                                   (Value4)) V(Val)) V(min_val, max_val)

It should work well for the "hundreds of columns" mentioned.

Though it introduces some extra complexity into the plan compared to just a simple compute scalar.

Jonathan Fite · Answer 2 · 2015-09-22T20:57:27.437

I would do something like this. You might need to edit the syntax depending on how you want to handle NULL vs empty string, and it assumes that VALUE is the same datatype for all fields.

IF OBJECT_ID('tempdb..#Test1') IS NOT NULL
BEGIN
    DROP TABLE #Test1
END

CREATE TABLE #Test1
    (
    TestID VARCHAR(10) NOT NULL PRIMARY KEY
    , Value1 VARCHAR(10) NULL
    , Value2 VARCHAR(10) NULL
    , Value3 VARCHAR(10) NULL
    , Value4 VARCHAR(10) NULL
    )

INSERT INTO #Test1
(TestID, Value1, Value2, Value3, Value4)
VALUES 
('f001', 'a1', 'a2', 'a2', 'a2')
, ('f002', 'a4', 'a4', 'a4', 'a5')
, ('f003', 'a2', 'a2', 'a2', NULL)
, ('f004', 'a1', 'a1', 'a1', 'a1')
, ('f005', 'a9', NULL, NULL, NULL)

SELECT TestID, Value1, Value2, Value3, Value4 FROM #Test1


;WITH CTE_Test AS
(
    SELECT TestID
        , Value1 AS Value
    FROM #Test1
    UNION ALL
    SELECT TestID
        , Value2 AS Value
    FROM #Test1
    UNION ALL
    SELECT TestID
        , Value3 AS Value
    FROM #Test1
    UNION ALL
    SELECT TestID
        , Value4 AS Value
    FROM #Test1
)
, CTE_TestDistinct AS
(
    SELECT DISTINCT TestID, Value 
    FROM CTE_Test 
    WHERE Value IS NOT NULL
)
SELECT CTE_TestDistinct.TestID
    , Issue = CASE WHEN COUNT(Value) = 1 THEN 'NoIssue'
                    ELSE 'MisMatch'
                    END
FROM CTE_TestDistinct
GROUP BY TestID
UNION ALL
SELECT TestID 
    , 'NoIssue'
FROM #Test1
WHERE NOT(TestID IN (SELECT C.TestID FROM CTE_TestDistinct C))


IF OBJECT_ID('tempdb..#Test1') IS NOT NULL
BEGIN
    DROP TABLE #Test1
END

Another method occurred to me. Again, this makes several assumptions about the data, but returns the same answer with the same source data while being effectively a one liner.

SELECT TestID
    , CASE WHEN AllHashed <> Value1RHashed THEN 'Mismatch' ELSE 'NoIssue' END
FROM (
SELECT TestID
    , AllHashed = HASHBYTES('md5',(ISNULL(Value1, COALESCE(Value1, Value2, Value3, Value4, '')) 
                                    + ISNULL(Value2, COALESCE(Value1, Value2, Value3, Value4, '')) 
                                    + ISNULL(Value3, COALESCE(Value1, Value2, Value3, Value4, '')) 
                                    + ISNULL(Value4,COALESCE(Value1, Value2, Value3, Value4, ''))))
    , Value1RHashed = HASHBYTES('md5', REPLICATE(COALESCE(Value1, Value2, Value3, Value4, ''), 4))
FROM #Test1
) D

score 0 · Answer 3 · edited Sep 23 '15 at 13:19

I would go for a CASE variation, 4 columns is still ok, just a bit more complicated due to the NULLs:

case when coalesce(Value1,Value2,Value3,Value4) = coalesce(Value2,Value3,Value4,Value1)
      and coalesce(Value2,Value3,Value4,Value1) = coalesce(Value3,Value4,Value1,Value2)
      and coalesce(Value3,Value4,Value1,Value2) = coalesce(Value4,Value1,Value2,Value3)
    then 'NoIssue'
   else 'MisMatch'
end

score -3 · Answer 4 · answered Sep 23 '15 at 13:14

Would this help?

    select test.*,                                       
           case when min(v1,v2,v3,v4) = '' then 'NoIssue'
           else 'MisMatch'                          
       end                                             
    from   test                                          
    where  max(v1,v2,v3,v4) <> min(v1,v2,v3,v4)

Compare each column in a row and return an error if any of them is different

4 Answers4