TSQL - How to return 2 values with one case statement?

Question

Is there a way to use one CASE statement and return 2 different values?

Following query has 2 CASE statements with same conditions.

select case when DA.value = 1 
   then da.Good else da.Bad end as Foo,
  case when DA.value = 1 
   then ot.Good else ot.Bad end as Bar,
from someTable DA (nolock)
  join otherTable OT (nolock) on OT...
where ...

Is there anyway, to specify that CASE statement once?
so that there is no need to keep both CASE statements in sync whenever the condition changes?

Answer 1

There's no way to do what you're describing. Not only is your case statement different for both cases, but you're returning values from totally different tables in each case.

Answer 2

Not sure if this is what you need, but you can do combinations like:

select
   case
      when da.value = 1 and ot.attributeValue = 1 then ot.good
      when da.value = 2 and ot.attributeValue = 3 then ot.good
      ...
      else ot.bad
   end Result
from
   someTable DA

   JOIN otherTable OT
      on (ot.id = da.id)

Ron