How to assign multiple values in CASE statement?

Question

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?

Using functions increase readability and maintainability but they will destroy performance. — Tim Schmelter, Mar 10 '16 at 11:18
create table Category (Categoryname ,FoodName) use join with your original table — Dgan, Mar 10 '16 at 11:18
In this case I value maintainability and readability over performance, as this script will only have to be run once a day, during the night. I expect no issues. — Pr0no, Mar 10 '16 at 11:20

score 5 · Answer 1 · answered Mar 10 '16 at 11:35

When doing things like this I tend to use a join with a table valued constructor:

SELECT  t.UserID,
        s.Snack,
        s.Drink
FROM    Table AS T
        LEFT JOIN
        (VALUES
            (1, 'Apple', 'Milk'),
            (2, 'Pear', 'Cola')
        ) AS s (Condition, Snack, Drink)
            ON s.Condition = CASE 
                                WHEN t.name IN ('A', 'B') THEN 1
                                WHEN t.name IN ('C', 'D') THEN 2
                            END;

I find this to be the most flexible if I need to add further conditions, or columns.

Or more verbose, but also more flexible:

SELECT  t.UserID,
        s.Snack,
        s.Drink
FROM    Table AS T
        LEFT JOIN
        (VALUES
            ('A', 'Apple', 'Milk'),
            ('B', 'Apple', 'Milk'),
            ('C', 'Pear', 'Cola'),
            ('D', 'Pear', 'Cola')
        ) AS s (Name, Snack, Drink)
            ON s.Name= t.name;

Tim Schmelter · Accepted Answer · 2016-03-10T11:51:48.900

Functions destroy performance. But you could use a common-table-expression(cte):

with cte as
(
    Select IsNameInList1 = case when name in ('A', 'B') 
                           then 1 else 0 end,
           IsNameInList2 = case when name in ('C', 'D') 
                           then 1 else 0 end,
           t.*
    from table
)
select
    userid
  , case when IsNameInList1=1 then 'Apple'
         when IsNameInList2=1 then 'Pear'
    end as snack
  , case when IsNameInList1=1 then 'Milk'
         when IsNameInList2=1 then 'Cola'
    end as drink
from
    cte
;

On this way you have only one place to maintain.

If query performance doesn't matter and you want to use a scalar valued function like this:

CREATE FUNCTION [dbo].[IsNameInList1] 
(
    @name varchar(100)
)
RETURNS bit
AS
BEGIN
DECLARE @isNameInList bit

BEGIN
    SET @isNameInList =  
    CASE WHEN @name in ('A', 'B')
    THEN 1
    ELSE 0 
    END
END

 RETURN @isNameInList 
END

Then you can use it in your query in this way:

select
    userid
  , case when dbo.IsNameInList1(name) = 1 then 'Apple'
         when dbo.IsNameInList2(name) = 1 then 'Pear'
    end as snack
from
    table
;

But a more efficient approach would be to use a real table to store them.

And if I need the same info in a later query, would a function not be better? — Pr0no, Mar 10 '16 at 11:26
@Pr0no: then you should consider to persist it in a real table. A function is not appropriate in most cases. Only if query performance doesn't matter it's an option. It's a black hole for the optimizer. — Tim Schmelter, Mar 10 '16 at 11:33
Query performance doesn't matter in this case. Could you show me what a function like this would look like? It's the first time I would use a function. Considering it a learning opportunity as well. — Pr0no, Mar 10 '16 at 11:35

Abdul Rasheed · Answer 3 · 2017-03-29T05:18:41.583

Hope this will help you

SELECT userid
        ,(CASE flag WHEN 1 THEN 'Apple' WHEN 2 THEN 'Pear' WHEN 3 THEN '..etc' END ) as snack
        ,(CASE flag WHEN 1 THEN 'Milk'  WHEN 2 THEN 'Cola' WHEN 3 THEN '..etc' END ) as drink
FROM    (
    SELECT userid
            ,(  CASE    WHEN name IN ('A', 'B') THEN 1 
                    WHEN name IN ('C', 'D') THEN 2 
                    WHEN name IN ('X', 'Y') THEN 3
                ELSE 0 END )    AS flag
    FROM table ) t

score 0 · Answer 4 · answered Oct 04 '19 at 20:19

0

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.

answered Oct 04 '19 at 20:19

alexherm

1,362
2
18
31

How to assign multiple values in CASE statement?

4 Answers4