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?

Can you clarify your scenario? For example, do the 100 different case conditions lead to 100 different applicable values for each of value1 and value2, or will many of the 100 different conditions produce the same results? Are the conditions all completely combinations of differing fields, or are they different possible values of a single field? — user359040
– user359040, Commented Dec 2, 2011 at 15:40
Yes, 100 different case condition lead to 100 different values for each value1 and value2. The conditions all combination of different fields.. many thanks — yzhang
– yzhang, Commented Dec 2, 2011 at 15:50
@yzhang : Are the results fixed? In that, every row that matches condition 1 will always have the exact same values in value1 and value2? — MatBailie
– MatBailie, Commented Dec 2, 2011 at 15:52
Yes, basically, I have 100 rules, the I need to go through the table to check each record, to fit these rules, if it fits, then I will return 3 values... however some records may fit mulitple rules. ideally all the result will return.. then later, I will base on other citeria to choose to keep one set of values for each record — yzhang
– yzhang, Commented Dec 2, 2011 at 16:01
@yzhang - With CASE only the first match will return values. If you want the possibility of multiple conditions mathcing each input row, you need to make each check indpendantly, and UNION the results together. Both @Yuck and I have answers that will fullfil that for you. — MatBailie
– MatBailie, Commented Dec 2, 2011 at 16:16

MatBailie · Accepted Answer · 2019-08-05 00:59:43Z

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

Yuck · Accepted Answer · 2011-12-02 15:32:21Z

9

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
;

answered Dec 2, 2011 at 15:32

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

3 Comments

MatBailie Over a year ago

Rather than joining (probably outer joins with coalesce statements), you probably what to UNION these together. With the assumption that only one condition is ever true for any given input record. Even then, this would involve scaning the source data set once for every condition, and so having 100 scans of the data.

Yuck Over a year ago

@Dems: Yes, that would work as well. The ELSE condition in the OP question is written ELSE <value1=a3, value3=b3>, so the columns aren't all identical. I "fixed" that in my answer, but it may not be a typo.

MatBailie Over a year ago

Good spot, I wouldn't assume a typo, it may be relevent, and so require NULLs in value2 for other results.

JNK · Accepted Answer · 2011-12-02 15:33:17Z

6

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.

answered Dec 2, 2011 at 15:33

JNK

65.5k15 gold badges127 silver badges138 bronze badges

2 Comments

user359040 Over a year ago

+1 Are there any conditions where it doesn't short-circuit?

JNK Over a year ago

@MarkBannister - yes but they are buggy (in SQL Server). Basically the only circumstance I have seen is where you do a comparison against an aggregate of a variable (CASE WHEN MAX(@var)...). It will evaluate them all but still return the right value.

yvtong · Accepted Answer · 2020-01-29 02:33:10Z

3

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>

answered Jan 29, 2020 at 2:33

yvtong

311 bronze badge

1 Comment

Manngo Over a year ago

SQL Server: THEN a1+','+b1.

HLGEM · Accepted Answer · 2011-12-02 15:36:55Z

2

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

answered Dec 2, 2011 at 15:36

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Comments

user359040 · Accepted Answer · 2011-12-02 15:36:03Z

1

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

answered Dec 2, 2011 at 15:36

user359040

Comments

Morteza ebrahim nejad · Accepted Answer · 2019-10-16 21:35:11Z

1

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

edited Oct 16, 2019 at 21:35

answered Sep 28, 2019 at 13:30

Morteza ebrahim nejad

112 bronze badges

1 Comment

Max Over a year ago

While the code might be easy to understand for people with experience, beginners might need an explanation. Please add an explanation what your code does and how it solves OPs question.

NiFiNiTe · Accepted Answer · 2020-10-31 20:53:26Z

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.

Angelo Fuchs · Accepted Answer · 2011-12-03 22:37:04Z

0

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

edited Dec 3, 2011 at 22:37

answered Dec 2, 2011 at 15:33

Angelo Fuchs

9,9911 gold badge39 silver badges73 bronze badges

3 Comments

MatBailie Over a year ago

And for the other 99 conditions? (This give 2 fields, but only for 1 condition)

Angelo Fuchs Over a year ago

Use UNION, one subquery per condition.

Angelo Fuchs Over a year ago

@Dems: Also you could use 'OR' here, (I edit my example to fit it)

Collectives™ on Stack Overflow

case statement in SQL, how to return multiple variables?

9 Answers 9

Comments

3 Comments

2 Comments

1 Comment

Comments

Comments

1 Comment

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

Comments

3 Comments

2 Comments

1 Comment

Comments

Comments

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related