How to assign multiple values in CASE statement?

user_330271 0
Viewed 23820

I need to assign two values to my select based on a CASE statement. In pseudo:

select
    userid
  , case
        when name in ('A', 'B') then 'Apple'
        when name in ('C', 'D') then 'Pear'
    end as snack
from
    table
;

I am assigning a value for snack. But lets say I also want to assign a value for another variable, drink based on the same conditions. One way would be to repeat the above:

select
    userid
  , case
        when name in ('A', 'B') then 'Apple'
        when name in ('C', 'D') then 'Pear'
    end as snack
  , case
        when name in ('A', 'B') then 'Milk'
        when name in ('C', 'D') then 'Cola'
    end as drink
from
    table
;

However, if I have to assign more values based on the same conditions, say food, drink, room, etc. this code becomes hard to maintain.

Is there a better way of doing this? Can I put this in a SQL function, like you would normally do in another (scripting) language and if so, could you please explain how?

case-statement sql-server sql
4 Answers

As Ganesh suggested in a comment, I recommend creating a mapping table for this and just do lookups. Much easier to interpet, maintain, and scale - all with better performance.

user_100125190
Related