Select case with another select after then

Question

Hi,

I am looking to have a transact sql statement going like this

I am trying to concatenate firstname and lastname from the table employee whenever isEmployee = 1

Thanks for helping

when 1 then
(
select firstname + ' '+ lastname
)
from tblEmployee

Pankaj Patel · Answer

Hi Schleid Alex, As following you can use Select case with another select after then : select case isEmployee when 1 then firstname + ' ' + lastname else firstname end as EmployeeName from tblEmployee Hope, this will help you!

Kirtesh Shah · Answer

Yes Agree with Pankaj, Ideally it is not possible. you can try the below query if it will solve your problem. select case IsEmployee when 1 then firstname + ' ' + lastname end as Fullname, firstname,lastname from tblemployee This query will give you result like this, You can filter your data based on NULL condition in code. If Fullname is not null then use fullname or use firstname and lastname. I hope it will help you to solve your problem.

Pankaj Patel · Answer

Select Case IsEmployee when 1 firstname + ' ' + Lastname else firstname, lastname end from tblemployee Ideally above query is not possible because if part is selecting/giving one column and another else part is returning two columns. And one select colunmn can' have either one and two column. I don't know whether it solves your problem or not but you can try following 2 ways: select firstname + ' ' + lastname as firstname, '' as lastname from tblEmployee where isEmployee = 1 union select firstname, lastname from tblEmployee where isEmployee 1 or select firstname + ' ' + lastname as FullName, '' as firstname, '' as lastname from tblEmployee where isEmployee = 1 union select '' as FullName, firstname, lastname from tblEmployee where isEmployee 1 Hope, this will help you!

Varun Kalidindi · Answer

Below is the answer you are looking for select case IsEmployee when 1 then firstname + ' ' + lastname else firstname end as FullName from Employee

Schleid Alex · Answer

Here is what I want to acheive I have a table which contains personalinformation for both employee and customer is this form GivenName, LegalName,IsEmployee and isCustomer when I am selecting customer I want the result to be in the form of GivenName + LegalName as BusinessName this is easy so far the tricky part is that employee can be also be customer in this case if IsEmployee = 1 then I just want FirstName and LastName businessName will be null when businessname is customer =1 firstname and lastname will also be null This is in the query result. I hope this make sense.

Schleid Alex · Answer

Thank you so much mr Patel you pointed in the right direction. but let me add up to the previous idea. I want to concatenate firstname and lastname when IsEmployee equal to 1 and not concatenate them when IsEmployee not equal to 1 I tried Select Case IsEmployee when 1 firstname + ' ' + Lastname else firstname, lastname end from tblemployee