Find maximum row value for each record and apply it to new column

Question

I'm trying to get rid of the NULL values and create a new column that would capture the Y (yes). Each record ID has a separate column for favorite color. There will never be a N (no) or more than 1 Y for a single record ID.

I attempted to do a select min on each column then group by record ID, with no luck. Any help would be appreciated!

Input:

RecordID	RecordDate	FavoriteColorYellow	Favorite ColorGreen	FavoriteColorPurple
1	1/1/2020	Y	NULL	NULL
2	2/8/2021	NULL	NULL	Y
3	2/10/2020	NULL	Y	NULL
4	3/1/2020	Y	NULL	NULL

Expected output:

RecordID	RecordDate	FavoriteColor
1	1/1/2020	Yellow
2	2/8/2021	Purple
3	2/10/2020	Green
4	3/1/2020	Yellow

Below code so you can reproduce the tables.

CREATE TABLE Records (
    RecordID int,
    RecordDate datetime);

CREATE TABLE Colors (
    RecordID int,
    FavoriteColorYellow varchar(1),
    FavoriteColorGreen varchar(1), 
    FavoriteColorPurple varchar(1), 
    );

INSERT INTO Records (RecordID, RecordDate)
VALUES (1, '1/1/2020'),
(2, '2/8/2020'),
(3, '2/10/2020'),
(4, '3/1/2020')

INSERT INTO Colors(RecordID, FavoriteColorYellow, FavoriteColorGreen, FavoriteColorPurple)
VALUES (1, 'Y', NULL, NULL),
(2, NULL, NULL, 'Y'),
(3, NULL, 'Y', NULL),
(4, 'Y', NULL, NULL)

I tried something like this using CASE but my actual data sets has about 10 columns and writing out all combinations seems a bit messy:

SELECT 
T1.[RecordID]
,T1.[RecordDate]
--,T2.[FavoriteColorYellow]
--,T2.[FavoriteColorGreen]
--,T2.[FavoriteColorPurple]
,CASE
    WHEN (T2.[FavoriteColorYellow] IS NULL OR T2.[FavoriteColorGreen] IS NULL) THEN T2.[FavoriteColorPurple]
    WHEN (T2.[FavoriteColorPurple] IS NULL OR T2.[FavoriteColorYellow] IS NULL) THEN T2.[FavoriteColorGreen]
    ELSE '0'
    END AS FAV

FROM [Records] T1
LEFT JOIN [Colors] as T2 ON T1.[RecordID] = T2.[RecordID]

Answer 1

A case will let you switch across the columns as needed and collapse to a single value.

case 'Y'
    when FavoriteColorGreen  then 'Green'
    when FavoriteColorPurple then 'Purple'
    when FavoriteColorYellow then 'Yellow'
    else 'NA' -- optional
end as FavoriteColor