How to make a query on multiple columns without writting all possible permutations?
You don't need loops, but one CASE per searched value:

select ...
from
 ( 
   select ...
      case when 'Bach' IN (col1,col2,col3,col4,col5) then 1 else 0 end +
      case when 'Joan' IN (col1,col2,col3,col4,col5) then 1 else 0 end +
      case when 'Mike' IN (col1,col2,col3,col4,col5) then 1 else 0 end as matches
   from tab
 ) dt
where matches &gt; 0
order by matches desc