Calculation in SQL Statement

I have a table containing a field called ChannelId. I want to break it out into two fields depending on the value. I tried this:

SELECT CustomerId, ChannelId = 1 as Chan1, ChannelId = 2 as Chan2 FROM ....

The goal being that I have two boolean columns representing whether the ChannelId field is of the approriate value. I get a syntax error.

Looking at the SQL Syntax I can't see any reason why I can't use an expression and then alias it as a new column name, but 开发者_Go百科SQL Server croaked on it. Am I doing something really dumb? How can I achieve this effect?

You have to perform an expression, not an assignment, and it depends on he flavor of SQL. Since you're using SQL Server (T-SQL):

SELECT CustomerId, (CASE WHEN ChannelId = 1 THEN 1 ELSE 0 END) as Chan1, (CASE WHEN ChannelId = 2 THEN 1 ELSE 0 END) as Chan2 FROM ....

There might be a better way to do it, but that's how I do it.

Try:

select
    CustomerId
  , case when ChannelId = 1 then 1 else 0 end as Channel_1
  , case when ChannelId = 2 then 1 else 0 end as Channel_2
from Customer_Channel ;

People have already said how to do this, using a CASE ... WHEN ... THEN style statement, but I'd just like to offer the opinion that this amounts to having business logic in the database. Typically I aim to keep the database purely for storage, and have business logic in a higher layer of my application.

I think this way because if you consider an application to have a layered architecture, you'd put your business logic in one layer, and one or more layers deeper would be your database. One or more layers higher would be your UI. "Business logic in the database" is bad in the same way it'd be bad to put business logic in the handler for a button_Click event on a form.

I appreciate this might not be appropriate for your particular circumstances on this day in 2011, but for future readers of this question, I hope it's of some use.