SQL Case with multiple values

Question

Is there a condensed form of the following statement?

SELECT Name, Case StatusID WHEN 1 THEN 'Alive' WHEN 2 THEN 'Alive' WHEN 3 THEN 'Alive' WHEN 4 THEN 'Dying' ELSE 'Dead' END FROM People

for example

CASE StatusID WHEN 1,2,3 THEN 'Alive'

or

CASE StatusID WHEN 1 OR 2 OR 3 THEN 'Alive'

Does this answer your question? CASE IN statement with multiple values — Vadzim
– Vadzim, Commented Dec 12, 2019 at 16:49
CASE StatusID WHEN 1,2,3 THEN ... END is valid by SQL Standard. Comma-separated predicates in simple CASE expression“ (F263). — Lukasz Szozda
– Lukasz Szozda, Commented Jul 26 at 12:34

Yahia · Accepted Answer · 2012-02-04 16:37:45Z

8

depending on the DB you use the following will do the trick

SELECT 
Name, 
Case WHEN StatusID  IN ( 1, 2, 3 ) THEN 'Alive' WHEN StatusID = 4 THEN 'Dying' ELSE 'Dead' END 
FROM People

answered Feb 4, 2012 at 16:37

Yahia

70.5k9 gold badges117 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eaolson · Accepted Answer · 2012-02-04 16:36:23Z

5

In Oracle, assuming statuid is never <= 0:

SELECT Name, CASE WHEN statusid < 4 THEN 'Alive'
                  WHEN statusid = 4 THEN 'Dying'
                  ELSE 'Dead' END AS some_alias
  FROM people

You can also use DECODE.

answered Feb 4, 2012 at 16:36

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

2 Comments

eaolson Over a year ago

You can't use inequalities, but you can do DECODE(1, 'Alive', 2, 'Alive', 3, 'Alive', 4, 'Dying', 'Dead'). In fact, that may actually be more compact.

Marcel Over a year ago

Sorry... didn't specify the DBMS. It's MSSQL 2008 R2, so no DECODE.

Collectives™ on Stack Overflow

SQL Case with multiple values

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related