>I have the typical structure > >SELECT >WHEN >CASE <expr1> THEN val1 >CASE <expr2> THEN val2 >... >END >FROM MY TABLE > >Is there a way or "trick" to return 2 values or columns in the same CASE >statement? > >Now I need to repeat the WHEN...END statements for the second column, using >the same <expr> but changing only the ><val>. It is a pain because the <expr> >are really long (several lines) and the SQL statement is more unreadable.
If you're on a reasonably recent version, what about using a CTE? That way, you would have WITH MyCTE(TmpResult) as (SELECT CASE WHEN <expr1> THEN 1 WHEN <expr2> THEN 2 END FROM MyTable) SELECT CASE TmpResult WHEN 1 THEN val1 WHEN 2 THEN val2 END, CASE TmpResult WHEN 1 THEN val3 WHEN 2 THEN val4 END FROM MyCTE HTH, Set
