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

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?

This question is related to sql sql-server

The answer is


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


or you can

SELECT
  String_to_array(CASE
    WHEN <condition 1> THEN a1||','||b1
    WHEN <condition 2> THEN a2||','||b2
    ELSE a3||','||b3
  END, ',') K
FROM <table>

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.


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 <condition 1>
    AND (<other conditions>)
  UNION
  SELECT
    b1,
    b2,
    b3,
    ...
  WHERE <condition 2>
    AND (<other conditions>)
  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.


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
;

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))

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 <condition 1> THEN
CAST((SELECT a1 AS value1, b1 AS value2 FOR XML PATH('')) AS XML)
WHEN <condition 2> 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 <table>) AS tmp

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