TransactSQL - Two case statements in One Select Statement Outputting to one Column

[adman29]

Hi all, 

I basically need to have two case statements outputting to two columns in Transact SQL. I have the first half here:

USE AdventureWorks2017
Select
	CASE
		WHEN PersonType = 'SC' then 'Store Contact'
		WHEN PersonType = 'IN' then 'Individual (retail) Customer'
		WHEN PersonType = 'SP' then 'Sales Person'
		WHEN PersonType = 'EM' then 'Employee (non-sales)'
		WHEN PersonType = 'VC' then 'Vendor Contact'
		WHEN PersonType = 'GC' then 'General Contact'
		ELSE ''
        END AS PersonTypeEvaluated

From Person.Person

I need to have it basically concatenate FirstName MiddleName and LastName in the second Case statement.

[Franck]

42 minutes ago, adman29 said:

Hi all, 

I basically need to have two case statements outputting to two columns in Transact SQL. I have the first half here:

USE AdventureWorks2017
Select
	CASE
		WHEN PersonType = 'SC' then 'Store Contact'
		WHEN PersonType = 'IN' then 'Individual (retail) Customer'
		WHEN PersonType = 'SP' then 'Sales Person'
		WHEN PersonType = 'EM' then 'Employee (non-sales)'
		WHEN PersonType = 'VC' then 'Vendor Contact'
		WHEN PersonType = 'GC' then 'General Contact'
		ELSE ''
        END AS PersonTypeEvaluated

From Person.Person

I need to have it basically concatenate FirstName MiddleName and LastName in the second Case statement.

Do 2 queries with union.

 

Example :

 

SELECT PersonType, '' AS 'Name' FROM Person WHERE PersontType <> 'IN'
UNION
SELECT PersonType, FirstName + ' ' + MiddleName + ' ' + LastName as 'Name' FROM Person WHERE PersontType == 'IN'

 

[adman29]

Ah good idea

Thank you

[Franck]

12 minutes ago, adman29 said:

Ah good idea

Thank you

just make sure the column name and type matches between the unions and it will work perfectly

[APileofRocks]

You should just be able to do these in the same statement.

 

USE AdventureWorks2017
Select
	CASE
		WHEN PersonType = 'SC' then 'Store Contact'
		WHEN PersonType = 'IN' then 'Individual (retail) Customer'
		WHEN PersonType = 'SP' then 'Sales Person'
		WHEN PersonType = 'EM' then 'Employee (non-sales)'
		WHEN PersonType = 'VC' then 'Vendor Contact'
		WHEN PersonType = 'GC' then 'General Contact'
		ELSE ''
        END AS PersonTypeEvaluated,
	CONCAT(FirstName, ' ', MiddleName, ' ', LastName) as FullName
FROM person p

The FullName column doesn't need a case statement, unless you want to account for weird things like "if there is no middle name then print this value instead" etc. Using the UNION is fine for AdventureWorks, but increase the size of the Person table to a million people and you will definitely see significant performance slowdown from scanning the entire Person table twice over.