;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')

, CT

AS

(

SELECT DISTINCT Record_id, Newcol

FROM Mysample

CROSS APPLY (SELECT Newcol FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol) ) n

WHERE Newcol IS NOT NULL

)

SELECT Record_id, MAX(Newcol) AS Newcol

FROM CT

GROUP BY Record_id

HAVING COUNT(*) = 1

UNION

SELECT Record_id, Newcol

FROM CT WHERE Newcol = 'AA'