Is it possible to return multiple columns using 1 case statement?

Question

I have 4 case statements that are exactly the same CASE criteria, but they all have different THEN/ELSE statements.

Is it possible to do this all in one, or do I need to separate these all out and copy and paste the code multiple times?

,CASE WHEN lm.Id IN ('1','2','3') THEN lm.name ELSE lm.Desc END AS [Column1]
,CASE WHEN lm.Id IN ('1','2','3') THEN '3' ELSE '1' END AS [Column2]
,CASE WHEN lm.Id IN ('1','2','3') THEN 'True' ELSE 'False' END AS [Column3]

Is it possible to do this with less code?

Jonathan Hall · Accepted Answer · 2011-06-27 20:54:57Z

9

I don't think this is possible in strict SQL. Some DB engines may support it as an extension. You could probably accomplish functionally the same thing through some other mechanism, though... possibly with a JOIN, or a UNION.

answered Jun 27, 2011 at 20:54

Jonathan Hall

80.5k19 gold badges162 silver badges207 bronze badges

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

1 Comment

Limey Over a year ago

Sql Server will fail if you try to seperate anything in a case with a comma, so most likely he is stuck with 3 case statements

p.campbell · Accepted Answer · 2011-06-27 20:57:41Z

1

Suggest UNIONing your resultsets. It won't get you fewer lines of code, but perhaps more readable.

SELECT [name], '3', 'True'
From Mytable WHERE ID IN ('1','2','3')
UNION 
SELECT [desc], '1', 'False'
From Mytable WHERE ID NOT IN ('1','2','3')

answered Jun 27, 2011 at 20:57

p.campbell

101k70 gold badges265 silver badges326 bronze badges

1 Comment

t-clausen.dk Over a year ago

+1 seems like the best way to solve this specific issue, I would use UNION ALL instead for better performance

Michele · Accepted Answer · 2013-10-09 16:13:34Z

1

Why don't you try to update the table using where? In the select statement of your question you can declare Column1, Column2 and Column3 as NULL and with two update statements change the values.

With "only" three columns depending on same case statement the code below doesn't save much typing (probably execution time..?) but it comes handy when you have more than 3...

UPDATE MyTable
      SET Column1 = lm.name,
          Column2 = '3',
          Column3 = 'True'
WHERE lm.Id IN ('1','2','3')

UPDATE MyTable
      SET Column1 = lm.Desc,
          Column2 = '1',
          Column3 = 'False'
WHERE lm.Id NOT IN ('1','2','3')

answered Oct 9, 2013 at 16:13

Michele

8,7937 gold badges51 silver badges75 bronze badges

Comments

Rob625 · Accepted Answer · 2011-06-27 20:57:35Z

0

For the example you give, I would not try to make any change. If your test ( WHEN ... THEN ) involved a lot more calculation, or if it was repeated a lot more often, you could consider setting up a subquery to evaluate it. But with only a small amount of repetition, why bother? The code you have is easy to read, and not expensive to execute.

answered Jun 27, 2011 at 20:57

Rob625

33 bronze badges

Collectives™ on Stack Overflow

Is it possible to return multiple columns using 1 case statement?

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related