How to conditionally return either one field or another?

Question

I have a

Customer

table. This table consists of fields

FirstName

,

LastName

, and

CompanyName

. The business logic is that if there is any value at all for

CompanyName

, a query should return

CompanyName

, otherwise, if

CompanyName

is either

NULL

or empty, it should return a combination of

FirstName

and

LastName

.

All I have as of now is...

select ID, (FirstName + ' ' + LastName) as EntityName from Customer

or

select ID, (CompanyName) as EntityName from Customer

How do I write such a

select

query to return one or the other in

EntityName

, based on whether a value exists for

CompanyName

?

Answer 1

Select ID
      ,EntityName = case when Isnull(CompanyName,'') <>'' then CompanyName  else FirstName + ' ' + LastName end
 From Customer

Sign up