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

SELECT Record_id, 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
WHERE Newcol IS NOT NULL

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'
)
SELECT m.Record_id, d.MAXVAL as NewCol
FROM Mysample m
CROSS APPLY (
	SELECT MAX(X.Newcol) as MAXVAL, MIN(X.Newcol) as MINVAL
	FROM (VALUES (m.Col1), (m.Col2), (m.Col3), (m.Col4), (m.Col5)) X (Newcol) 
) d
WHERE d.MAXVAL = d.MINVAL
;