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 it all in one, or do I need to separate it all, copy and paste the code several 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?

Answer 1

I do not think this is possible in strict SQL. Some database engines may support it as an extension. Perhaps you could accomplish functionally the same thing with some other mechanism, though ... perhaps with JOIN or UNION.

Answer 2

Offer UNION your results. This will not result in 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') 

Answer 3

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

With “only” three columns, depending on the same case code below does not save much typing (maybe runtime ...?), But it works 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') 

Answer 4

For the example you are giving, I would not try to make any changes. If your test (WHEN ... THEN) involved much more calculations, or if it was repeated much more often, you might consider creating a subquery to evaluate it. But with few reps, why bother? The code you have is easy to read and not expensive to execute.