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
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.
Source: Stackoverflow.com