CODEWITHSUNDEEP
sqlsql-servert-sql
mameesh
mameesh

Reputation: 3761

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

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?

Upvotes: 6

Views: 6327

Answers (4)

Michele
Michele

Reputation: 8753

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

Upvotes: 1

p.campbell
p.campbell

Reputation: 100577

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

Upvotes: 1

Rob625
Rob625

Reputation: 3

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.

Upvotes: 0

Jonathan Hall
Jonathan Hall

Reputation: 79604

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.

Upvotes: 9

Related Questions