CODEWITHSUNDEEP
sqlsybase
VoodooChild
VoodooChild

Reputation: 9784

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 null
    end
from Person person

Upvotes: 4

Views: 5775

Answers (3)

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195992

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

Upvotes: -1

Martin Smith
Martin Smith

Reputation: 453192

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.

Upvotes: 1

Thomas
Thomas

Reputation: 64645

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

Upvotes: 4

Related Questions