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;