SQL - Search multiple values across multiple columns to create a flag

user_13072665 0
Viewed 181

I am trying to search for multiple values across multiple columns so I can create an inclusion criteria flag, but can't figure out the best way. I need to create a flag, so I am trying to not have to use a where statement

I have this in my code now for only one value and it works: 

when 'a' in (col1, col2, col3, col4) 
then 1 
else 0 
end as col_a

Would like to do something like, but can't figure it out:

when 'a', 'b', 'c' in (col1, col2, col3, col4) 
then 1 
else 0 
end as col_all

Any help is appreciated! Thanks!

presto sql
2 Answers

Is this what you want?

(case when 'a' in (col1, col2, col3, col4) and
           'b' in (col1, col2, col3, col4) and
           'c' in (col1, col2, col3, col4) 
      then 1 else 0
 end) as col_all

It is a little unclear if you want all to match or any to match. If the latter, use or rather than and.

user_11440350

Try this

case array_except(array['a','b','c'],array[col1,col2,col3,col4]) 
when array[] 
then 1 
else 0 
end as col_all

enter image description here

user_63364790
Related