Sql Server view - Select certain column depending on the value of another column

I have a table 'Products' that has these columns

ProductId

OriginalPrice

SalePrice

IsOnSale (bit)

Is it possible to create a view 'ProductsView' that has these columns

ProductId

Price

where price is either OriginalPrice or SalePr开发者_开发技巧ice depending on the value of IsOnSale?

Thanks in advance!

Yes, use a case statement:

Create view CurrentPrice AS
SELECT ProductId
     , Price = CASE 
WHEN IsOnSale = 1 THEN SalePrice
ELSE OriginalPrice
END