SQL-Server CONCAT case

Question

I have the following exercise:

concatenate first, middle, last name and name suffix to form the customer’s name in the following format: FirstName [MiddleName.] LastName[, Suffix]. Note that NULL values should be omitted.

I interpret this as the following scenario (created the table from the image and inserted some values):

Please find my sample data below, name is #TB_Customer The column CustomerName is the expected result and should be of form

• FirstName MiddleName.LastName, Suffix if i have enteries for all the fields.

• MiddleName and Suffix can be optional, so the cases are:

• If there is a suffix but not a MiddleName then CustomerName
  should be of form Firstname LastName,Suffix

• If there is a MiddleName but not a suffix then CustomerName
  should be of form FirstName MiddleName.LastName

• If both MiddleName and Suffix are null then CustomerName should be of form FirstName LastName)

This is what i'm getting:

But as you can see the CustomerName case query I wrote doesn't work as expected (please see the cases above with bullets)

The query I wrote to get the CustomerName column is:

SELECT 
(case
when (MiddleName is not null and Suffix is not null)  then
CONCAT(c.FIRSTNAME,' ', c.MiddleName,'.', c.LASTNAME, ', ',Suffix)  
when (MiddleName is null and suffix is null) then 
CONCAT(c.FIRSTNAME,' ' ,c.LASTNAME) 
when (MiddleName is null and Suffix is not null )then
concat (c.FirstName, ' ', c.LastName, ', ',Suffix )  
when (Suffix is null and MiddleName is not null) then
concat (c.FirstName, ' ',MiddleName,'.',LastName)
end 
)AS CustomerName
,c.*
FROM   #TB_Customer c;

I have 2 questions:

• Did I understand the exercise and do i have a good logic?
• Where have I made a mistake and what's the correct query?

Using SQL-Server 2012

edit

to recreate my scenario please see the code below (sorry for not linking a fiddle but the website is not responding at my current location)

CREATE TABLE #TB_Customer
(
CustomerID int , --PK
Title varchar(50),
FirstName varchar(50),
MiddleName varchar(50),
LastName varchar(50),
Suffix varchar(50),
EmailAddress varchar(50),
Phone varchar(50),
Gender varchar(50),
Birthdate varchar(50),
--no fk
PRIMARY KEY (CustomerID)
)

insert into #TB_Customer values
('1','Sir','John','Jacob','Adams','St','[email protected]','0677731235','M','1989-04-06'),
('2','Mr.','Russel','Thyrone','Peterson','pr','[email protected]','555-010405','M','1963-02-01'),
('3','Ms.','Anne','Candice','Acola','aca','[email protected]','07408989989','F','1988-05-19'),
('4','Mrs.','Sophia','Veronika','Turner','tvs','[email protected]','0423423887','F','1983-06-20'),
('5','Ms','Margaret','','Delafleur','','[email protected]','233223355','Female','1982-02-25'),
('6','Mrs','Jessica','Luana','Cruz','','[email protected]','787876631','Female','1922-05-05'),
('7','Mr','Dyrius','','Cruz','dc','[email protected]','0673332211','Male','1987-03-01')

update #TB_Customer
set Gender = 'Male' where Gender = 'M'
update #TB_Customer
set Gender = 'Female' where Gender = 'F'

Answer 1

Something like this should work as well...

SELECT concat(firstname
     ,CASE WHEN ISNULL(middlename,'') <> '' THEN ' '+middlename+'.'
        WHEN ISNULL(middlename,'') <> '' AND ISNULL(suffix,'') = '' THEN '.' 
        ELSE ' ' END
     ,lastname
     ,CASE WHEN ISNULL(suffix,'') <> '' THEN ', '+suffix END)
FROM #TB_Customer

OUTPUT:

John Jacob.Adams, St
Russel Thyrone.Peterson, pr
Anne Candice.Acola, aca
Sophia Veronika.Turner, tvs
Margaret Delafleur
Jessica Luana.Cruz
Dyrius Cruz, dc
John Adams, St