5

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.

  • 4
    Why does the fourth identifier need to return "mismatch"; they seem to match to me? Are values in the Identifier column really not unique? – Max Vernon Sep 22 '15 at 19:14
  • 2
    Yes I'm really curious about the second aspect of Max's question - is the 5th row really supposed to read f002, or should that be f005? If it's supposed to be f002 then that is a really awkward and misleading choice for a column name. – Aaron Bertrand Sep 22 '15 at 20:39
  • This is not how relational data is supposed to work – BlueRaja - Danny Pflughoeft Sep 22 '15 at 23:09
7

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

enter image description here

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

enter image description here

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.

enter image description here

  • 1
    A minor optimization in the case of many columns might be to compute MIN and MAX (which don't require a sort to be computed) and compare them. If they are different, I think that's logically equivalent to asserting that COUNT(DISTINCT) > 1 – Geoff Patterson Sep 23 '15 at 1:53
2

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
  • The last row in your sample data is f005; whereas the OP's data shows f002 for that row. Presumably you're assuming the TestID values are unique? – Max Vernon Sep 22 '15 at 20:33
  • That's pretty much why. There needs to be at least one primary key to return, otherwise what do you do when you have one that has matches all the way across and another that does not. Insufficient data to process. In combination with the error discussing why f004 should be mismatch when the data is the same all the way across, I thought it a fair assumption. – Jonathan Fite Sep 22 '15 at 20:45
0

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
-3

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)          
  • 2
    I don't think that min() and max() functions accept multiple parameters. If SQL Server had LEAST() and GREATEST(), yes, this might work. – ypercubeᵀᴹ Sep 23 '15 at 13:15

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.