Can I select multiple columns depending on a SELECT CASE?

Based on the SELECT CASE below I want to show two separate columns within one WHEN. Is this possible?

The two commented out lines below are something like what I would like to have. I am sure it is something very simple I am missing. As it is, there is a 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 n开发者_开发知识库ull
    end
from Person person

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

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.

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