Select case with another select after then

C# Corner

Schleid Alex

5y1k.NET

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Varun Kalidindi5y ago
Below is the answer you are looking for

select case IsEmployee
    when 1
        then firstname + ' ' + lastname
    else firstname
    end as FullName
from Employee
+2
Schleid Alex5y ago
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.
+2
Kirtesh Shah5y ago
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.
+3
Pankajkumar Patel5y ago
1. Select Case IsEmployee
2. when 1
3. firstname + ' ' + Lastname
4. else
5. firstname, lastname
6. end
7. 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:
1. select firstname + ' ' + lastname as firstname, '' as lastname from tblEmployee where isEmployee = 1
2. union
3. select firstname, lastname from tblEmployee where isEmployee <> 1
or
1. select firstname + ' ' + lastname as FullName, '' as firstname, '' as lastname from tblEmployee where isEmployee = 1
2. union
3. select '' as FullName, firstname, lastname from tblEmployee where isEmployee <> 1
Hope, this will help you!