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  lt condition 1 gt  THEN  lt value1 a1  value2 b1 gt      WHEN  lt condition 2 gt  THEN  lt value1 a2  value2 b2 gt      ELSE  lt value1 a3  value3 b3 gt    END FROM  lt table gt    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

User · Answer

CASE by definition only returns a single value   Ever   It also  almost always  short circuits  which means if your first condition is met no other checks are run

User · Answer

or you can   SELECT   String to array CASE     WHEN  lt condition 1 gt  THEN a1       b1     WHEN  lt condition 2 gt  THEN a2       b2     ELSE a3       b3   END       K FROM  lt table gt

User · Answer

You could use a subselect combined with a UNION  Whenever you can return the same fields for more than one condition use OR with the parenthesis as in this example   SELECT   FROM    SELECT val1  val2 FROM table1 WHERE  condition1 is true                                       OR  condition2 is true   UNION SELECT   FROM    SELECT val5  val6 FROM table7 WHERE  condition9 is true                                       OR  condition4 is true

User · Answer

Depending on your use case  instead of using a case statement  you can use the union of multiple select statements  one for each condition  My goal when I found this question was to select multiple columns conditionally  I didn t necessarily need the case statement  so this is what I did  For example    SELECT     a1      a2      a3            WHERE  lt condition 1 gt      AND   lt other conditions gt     UNION   SELECT     b1      b2      b3            WHERE  lt condition 2 gt      AND   lt other conditions gt     UNION   SELECT       -- and so on   Be sure that exactly one condition evaluates to true at a time  I m using Postgresql  and the query planner was smart enough to not run a select statement at all if the condition in the where clause evaluated to false  i e  only one of the select statement actually runs   so this was also performant for me

User · Answer

The basic way  unfortunately  is to repeat yourself   SELECT   CASE WHEN  lt condition 1 gt  THEN  lt a1 gt  WHEN  lt condition 2 gt  THEN  lt a2 gt  ELSE  lt a3 gt  END    CASE WHEN  lt condition 1 gt  THEN  lt b1 gt  WHEN  lt condition 2 gt  THEN  lt b2 gt  ELSE  lt b3 gt  END FROM     lt table gt     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    lt table gt  CROSS APPLY        SELECT a1  b1 WHERE  lt condition 1 gt     UNION ALL    SELECT a2  b2 WHERE  lt condition 2 gt     UNION ALL    SELECT a3  b3 WHERE  lt condition 3 gt        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  lt condition1 gt  THEN 1          WHEN  lt condition2 gt  THEN 2          WHEN  lt condition3 gt  THEN 3                       ELSE                   100     END AS condition id          FROM      lt table gt        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

User · Answer

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

User · Answer

You can return multiple value inside a xml data type in  case  expression  then extract them  also  else  block is available  SELECT  xmlcol value   value1  1     NVARCHAR MAX    AS value1  xmlcol value   value2  1     NVARCHAR MAX    AS value2 FROM  SELECT CASE WHEN  lt condition 1 gt  THEN CAST  SELECT a1 AS value1  b1 AS value2 FOR XML PATH      AS XML  WHEN  lt condition 2 gt  THEN CAST  SELECT a2 AS value1  b2 AS value2 FOR XML PATH      AS XML  ELSE CAST  SELECT a3 AS value1  b3 AS value2 FOR XML PATH      AS XML  END AS xmlcol FROM  lt table gt   AS tmp

User · Answer

In a SQL CASE clause  the first successfully matched condition is applied and any subsequent matching conditions are ignored

User · Answer

In your case you would use two case staements  one for each value you want returned

[sql] case statement in SQL, how to return multiple variables?

Examples related to sql

Examples related to sql-server