CODEWITHSUNDEEP
t-sql
SF Developer
SF Developer

Reputation: 5394

TSQL Comma Delimited fields on same row

Imagine you have a User table with FirstName, MiddleName, LastName.
For the sake of this sample only FirstName is mandatory.

What's the fastest way to concatenate these 3 fields and separate them with a "," when their value is NOT null, so:

If only FirstName is NOT null, we should get "John" as result.
If only FirstName and LastName are not null, we should have "John,Wayne" as result.
If FirstName, MiddleName and LastName are not null, we should have "John,M,Wayne" as result.

Thanks in advanced.

Upvotes: 0

Views: 113

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44346

2 different methods

--syntax
select Firstname + isnull(',' +Middlename, '') + coalesce(',' +LastName, '')
from 
--testdata
(select 'John' firstname, 'W' middlename, 'Wayne' lastname
union all select 'John' firstname, null middlename, 'Wayne' lastname
union all select 'John' firstname, 'W' middlename, null lastname) a

Upvotes: 0

Arion
Arion

Reputation: 31249

Maybe something like this:

FirstName +(CASE WHEN MiddleName IS NULL THEN '' ELSE ','+MiddleName END)
+(CASE WHEN LastName IS NULL THEN '' ELSE ','+LastName END)

Upvotes: 2

Related Questions