Thanks for the replies. I forgot to mention one further complication that does occur in the real data: You can see the same value in different source columns, but have that map to a different output value depending on which source column the value is in. For example:

WHEN source_column_1 = 'ABC' then 'XYZ'

WHEN source_column_3 = 'ABC' then '999'

Because of that condition, I don't think that the potential solution from Eirikur will work. (Or I'm just not seeing how to manage that scenario using that methodology.)