CODEWITHSUNDEEP
sqlsql-server
NateSHolland
NateSHolland

Reputation: 1170

SQL Server Case Statements

I have a question and I cant figure it out, it should be pretty quick but I haven't seen anything like it yet. So here's my main problem, I have a case statement which defines a team based on a manager so it looks like:

"team" = case
    when manager = 'manager1' then 'team1'
    ...
    when manager = 'managerN' then 'teamN'
    else 'Other'
end

Then I want to find a way to make a new column "proj" so that it is project unless team is 'Other' so I would like it to look like:

"proj" = case
    when team = 'Other' then 'Other'
    else project
end

but I keep getting error where the syntax is incorrect or it says team is not a valid column. Any ideas?

Upvotes: 1

Views: 180

Answers (1)

granadaCoder
granadaCoder

Reputation: 27852

Use Northwind
GO



select 

[Hemisphere] = case
    when derived1.Continent = 'Europe' then 'Eastern'
    when derived1.Continent = 'North America' then 'Western'
    when derived1.Continent = 'South America' then 'Western'
    else 'Other Hemisphere'
end 
, derived1.Continent
, derived1.Country
from (

select c.Country , 
[Continent] = case
    when c.Country = 'Germany' then 'Europe'
    when c.Country = 'France' then 'Europe'
    when c.Country = 'Sweden' then 'Europe'
    when c.Country = 'Denmark' then 'Europe'
    when c.Country = 'Finland' then 'Europe'
    when c.Country = 'Switzerland' then 'Europe'
    when c.Country = 'Poland' then 'Europe'
    when c.Country = 'Norway' then 'Europe'
    when c.Country = 'Ireland' then 'Europe'
    when c.Country = 'Austria' then 'Europe'
    when c.Country = 'Italy' then 'Europe'
    when c.Country = 'Portugal' then 'Europe'
    when c.Country = 'Belgium' then 'Europe'
    when c.Country = 'Spain' then 'Europe'
    when c.Country = 'UK' then 'Europe'
    when c.Country = 'Spain' then 'Europe'
    when c.Country = 'Mexico' then 'North America'
    when c.Country = 'USA' then 'North America'
    when c.Country = 'Canada' then 'North America'
    when c.Country = 'Brazil' then 'South America'
    when c.Country = 'Argentina' then 'South America'
    when c.Country = 'Venezuela' then 'South America'
    else 'Other Continent'
end

from [dbo].[Customers] c
) as derived1

Upvotes: 2

Related Questions