Sql-server – Best way to compare multiple non null/non zero fields in a row in T-SQL

querysql serversql-server-2008-r2t-sql

I need to compare figures across a row and show where there is any mismatch, excluding zero and null values. So in the example, ids 1, 4 and 5 would be returned. What is the most painless way of doing this without a ton of where and or statements?

CREATE TABLE #XX
(
   ID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
   VAL1  INT, 
   VAL2  INT, 
   VAL3  INT, 
   VAL4  INT, 
   VAL5  INT
)

INSERT INTO #XX (VAL1,VAL2,VAL3,VAL4,VAL5) VALUES
(5,10,NULL,17,12),
(12,12,12,NULL,0),
(15,15,15,15,15),
(3,NULL,6,0,NULL),
(10,NULL,15,0,20)

Cheers.

Best Answer

CREATE TABLE #XX
(ID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY, VAL1  INT, VAL2  INT, VAL3  INT, VAL4  INT, VAL5  INT)

INSERT INTO #XX (VAL1,VAL2,VAL3,VAL4,VAL5) VALUES
(5,10,NULL,17,12),
(12,12,12,NULL,0),
(15,15,15,15,15),
(3,NULL,6,0,NULL),
(10,NULL,15,0,20)

--First Unpivot the values so we can join the resulting values back to itself for comparisons. 
--This will also eliminate NULL values from the comparison
;WITH cte AS (
SELECT id, ValueNumber, Value
FROM
(SELECT id, val1,val2,val3,val4,val5 from #XX) v
UNPIVOT
(Value FOR ValueNumber IN(val1,val2,val3,val4,val5))
AS unpvt)

--SELECT only distinct ID's
SELECT DISTINCT v1.id FROM cte v1

--Join the table back to itself based on the ID column
--so we can compare values within each row
INNER JOIN cte v2 ON v1.id = v2.id

--Check the values are different
WHERE v1.value <> v2.value

--Ensure we are only checking between different columns
AND v1.ValueNumber <> v2.ValueNumber

--Check neither value is 0
AND v1.Value <> 0
AND v2.Value <> 0

dbfiddle

The above seems to do what you need - if you don't know the particular columns you'll need to UNPIVOT for then this will need to be reformed into a dynamic UNPIVOT script.

Related Question