I reckon the OP is having some difficulty simplifying the real problem. Here's a solution to the "new" requirements;

;WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5) AS (

SELECT 101, 'A', 'A', 'B', 'A', 'A' 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, ISNULL(OldCol, Newcol)

FROM Mysample

CROSS APPLY (

SELECT Newcol = CASE

WHEN MAX(Newcol) = MIN(Newcol) THEN MAX(Newcol)

ELSE NULL END

FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol)

) d

CROSS APPLY (SELECT Oldcol = CASE WHEN 'AA' IN (Col1, Col2, Col3, Col4, Col5) THEN 'AA' ELSE NULL END) y

WHERE Newcol IS NOT NULL

OR Oldcol IS NOT NULL