case statement in SQL, how to return multiple variables?

Question

I would like to return multiple values in my case statement, such as :

SELECT   CASE     WHEN <condition 1> THEN <value1=a1, value2=b1>     WHEN <condition 2> THEN <value1=a2, value2=b2>     ELSE <value1=a3, value3=b3>   END FROM <table> 

Of course I can write the case condition multiple times, each time return one value. However, as I have many condition need to fit, say 100. It is not good to repeat case condition again and again.

Another question I would like to ask, what happend if one record fit multiple condition? does that mean it will return all of them or just the last one? e.g. condition 1 may become a subset of condition 2. what will happen?

Answer 1

The basic way, unfortunately, is to repeat yourself.

SELECT   CASE WHEN <condition 1> THEN <a1> WHEN <condition 2> THEN <a2> ELSE <a3> END,   CASE WHEN <condition 1> THEN <b1> WHEN <condition 2> THEN <b2> ELSE <b3> END FROM    <table>  

Fortunately, most RDBMS are clever enough to NOT have to evaluate the conditions multiple times. It's just redundant typing.

In MS SQL Server (2005+) you could possible use CROSS APPLY as an alternative to this. Though I have no idea how performant it is...

SELECT   * FROM   <table> CROSS APPLY   (    SELECT a1, b1 WHERE <condition 1>    UNION ALL    SELECT a2, b2 WHERE <condition 2>    UNION ALL    SELECT a3, b3 WHERE <condition 3>   )   AS case_proxy 

The noticable downside here is that there is no ELSE equivalent and as all the conditions could all return values, they need to be framed such that only one can ever be true at a time.

EDIT

If Yuck's answer is changed to a UNION rather than JOIN approach, it becomes very similar to this. The main difference, however, being that this only scans the input data set once, rather than once per condition (100 times in your case).

EDIT

I've also noticed that you may mean that the values returned by the CASE statements are fixed. All records that match the same condition get the exact sames values in value1 and value2. This could be formed like this...

WITH   checked_data AS (   SELECT     CASE WHEN <condition1> THEN 1          WHEN <condition2> THEN 2          WHEN <condition3> THEN 3          ...          ELSE                   100     END AS condition_id,     *   FROM     <table> ) ,   results (condition_id, value1, value2) AS (    SELECT 1, a1, b1    UNION ALL    SELECT 2, a2, b2    UNION ALL    SELECT 3, a3, b3    UNION ALL    ...    SELECT 100, a100, b100 ) SELECT   * FROM   checked_data INNER JOIN   results     ON results.condition_id = checked_data.condition_id 

Answer 2

A CASE statement can return only one value.

You may be able to turn this into a subquery and then JOIN it to whatever other relations you're working with. For example (using SQL Server 2K5+ CTEs):

WITH C1 AS (   SELECT a1 AS value1, b1 AS value2   FROM table   WHERE condition1 ), C2 AS (   SELECT a2 AS value1, b2 AS value2   FROM table   WHERE condition2 ), C3 AS (   SELECT a3 AS value1, b3 AS value2   FROM table   WHERE condition3 ) SELECT value1, value2 FROM -- some table, joining C1, C2, C3 CTEs to get the cased values ;