Case When over 2 columns return results into 1 output column I have an issue in SQL Server (using 2014 but not sure that's relevant).  I have 2 variables Q20 and Q46 which depending on result I want to output a value to the same column for each result.  For example (The promoter column being the one that is generated with the Case expression)

Rf........Q20.........Q46........Promoter

1.............0.............9.............1

2.............8.............0.............1

3.............6.............0.............0

The Query I am trying to goes something like this (but obviously this isn't working):

select * 
,case Q20  
    when '8' then 1 
    when '9' then 1 
    else 0 end
,case Q46
    when '8' then 1 
    when '9' then 1 
    Else 0
end as Promoter
    from ##csat_resp


I have tried various connotations of this but nothing works that will bring both the results into one column (Promoter).

I refuse to believe this isn't possible and must be down to my lack of knowledge.

Please help!!
Something like this (untested) should work for you:

select
  *,
  case q20
    when '8' then 1
    when '9' then 1
    else case q46
      when '8' then 1
      when '9' then 1
      else 0
    end
  end as promoter
from ##csat_resp;


You could also write the same logic as:

case when q20 in ('8','9') or q46 in ('8','9') then 1 else 0 end


or in SQL Server 2012 and later:

IIF(Q20 IN (8, 9) OR T.Q46 IN (8, 9), 1, 0)