SQL: CASE and selecting multiple COLUMS

Question

Hi Guys,

How can I do this: Based on the select Case below I want to show TWO separate columns within one WHEN?

The two lines below which are commented is what something I would like to have and I am sure it is something very simple I am missing....however i get syntax error

select 
    person.FirstName,
    person.LastName,
    CASE 
        --WHEN substatus is not null then 'HasSubstatus', null
        --else null, 'DoesNotHaveSubstatus'
        WHEN substatus is not null then 'HasSubstatus'
        else null
    end
from Person person

Answer 1

You cannot do it in a single Case expressions as a Case expression returns a single value. You must use two Case expressions.

Select  person.FirstName
    , person.LastName
    , Case When substatus is not null Then 'HasSubstatus' End As [HasSubstatus]
    , Case When substatus is null Then 'DoesNotHaveSubstatus' End As [DoesNotHaveSubstatus]
From Person person

Answer 2

You can't.

You would need to repeat the case statement if you mean that you want to apply the same conditional logic to 2 columns or have it as a separate query if you mean that in one case it should return two columns and in the other case one column.

Answer 3

You could do it with if() and isnull() (this syntax would work only in mySql)

select 
    person.FirstName,
    person.LastName,
    IF( ISNULL(substatus),'HasSubstatus', null ) AS DoesHave,
    IF( ISNULL(substatus),null,'DoesNotHaveSubstatus'  ) AS DoesNotHave,
from Person person