asked on

Select Case concatenation

How can I concatenate two fields in select and one of the two in Case statement ?

See the following -

select title, "Contract Status" =
case
when contract = 1 then "Contract"
when contract = 0 then "No Contract"
end,
hireDate
from titles

Change the above one to -

select title + "Contract Status" =
case
when contract = 1 then "Contract"
when contract = 0 then "No Contract"
end,
hireDate
from titles

I'll got "Incorrect syntax near '=' if I use the above at statement.

How can concatenate the case field? I'm using SQL Server 2005.

THanks for any help.

Simone B

You don't want to use the alias when concatenating. Try this:

select title +
case
when contract = 1 then "Contract"
when contract = 0 then "No Contract"
end,
hireDate
from titles

Anuradha Goli

select title +
    case
         when contract = 1 then 'Contract'
          when contract = 2 then 'No Contract'
    end as 'Contract Status',
    hireDate
from titles

Open in new window

Hope this works

minglelinch

ASKER

Thanks a lot. It works.. I forgot to mention one more thing, I need to add something like else case.
something like -

select title +
case
when contract = 1 then 'Contract'
when contract = 2 then 'No Contract'
When ANY OTHER VALUE then '' (How to this logic)
end as 'Contract Status',
hireDate
from titles

How add the logic? Thanks.

ASKER CERTIFIED SOLUTION

Simone B

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

minglelinch

ASKER

Thanks!