SQL Server: searched case statement

For each found row, I want to output a derived value based on the content of 1 or more columns.

As ever, we create a working table from Adventureworks (Product => t_product in this example), and play with that. See MSDN for the specifics on the Searched Case statement

use AdventureWorks2014
go
if exists (select 1 from sys.tables where name = 't_product') begin; drop table t_product; end;
select * into t_product from [AdventureWorks2014].[Production].[Product]
select ProductID, Name, MakeFlag, FinishedGoodsFlag,
 MadeStatus = case 
 when MakeFlag = 0 and FinishedGoodsFlag = 0 then
 'Not started'
 when MakeFlag = 1 and FinishedGoodsFlag = 0 then
 'StartedNotFinished'
 when MakeFlag = 1 and FinishedGoodsFlag = 1 then
 'AllDoneAndDusted'
 else
 'WOH!WasNotExpectingThat'
 end 
from t_product;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s