I was thinking this could work
;WITH MySample AS (
SELECT 101 AS Record_id, 'A' AS Col1, 'A' AS Col2, 'B' AS Col3, 'A' AS Col4, 'A' AS Col5 UNION ALL
SELECT 102, 'B', 'B', NULL, NULL, 'B' UNION ALL
SELECT 103, 'C', 'C', 'C', NULL, 'C' UNION ALL
SELECT 104, 'D', 'C', 'D', 'D', 'D' UNION ALL
SELECT 105, 'AA', 'A', 'A', 'A', 'A' UNION ALL
SELECT 106, 'B', 'C', 'A', 'AA', 'D')
SELECT Record_id, Newcol
FROM Mysample
CROSS APPLY (
SELECT Newcol = CASE WHEN MAX(Newcol) = 'AA' THEN 'AA' WHEN MAX(Newcol) = MIN(Newcol) THEN MAX(Newcol) ELSE NULL END
FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol)
) d
WHERE Newcol IS NOT NULL
Which will return 105, but not 106
Wierd that 'AA' is not larger than 'D' The problem here is your using min/max functions on Strings which does not work for this situation.
If you have the ability to influence the design now you should consider changing to Numeric values in these tables.
Consider this simple test, which returns B as the max for Test 2 and not the significantly longer string of A's
From BOL
For character columns, MAX finds the highest value in the collating sequence.
sqlSELECT Test, MIN(Val) AS MinVal, MAX(Val) AS MaxVal
FROM (SELECT 1 AS Test, 'A' AS Val UNION ALL
SELECT 1 AS Test, 'B' AS Val UNION ALL
SELECT 1 AS Test, 'C' AS Val UNION ALL
SELECT 2 AS Test, 'A' AS Val UNION ALL
SELECT 2 AS Test, 'AAAAAAAAAA' AS Val UNION ALL
SELECT 2 AS Test, 'B' AS Val) t
GROUP BY Test