CODEWITHSUNDEEP
sqlsql-server
yzhang
yzhang

Reputation: 541

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?

Upvotes: 40

Views: 186905

Answers (9)

NiFiNiTe
NiFiNiTe

Reputation: 69

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.

Upvotes: 1

yvtong
yvtong

Reputation: 21

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>

Upvotes: 2

Morteza ebrahim nejad
Morteza ebrahim nejad

Reputation: 11

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

Upvotes: 1

MatBailie
MatBailie

Reputation: 86725

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

Upvotes: 26

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

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

Upvotes: 0

HLGEM
HLGEM

Reputation: 96572

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

Upvotes: 2

user359040
user359040

Reputation:

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

Upvotes: 1

JNK
JNK

Reputation: 65157

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.

Upvotes: 6

Yuck
Yuck

Reputation: 50855

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
;

Upvotes: 9

Related Questions