SQL - Split different column names display with separator

In some situation, we need to show separator for firstname and middlename. This can be comma if there some values.

Lets take sample of employees data as below

With EmployeeCTE AS (

    SELECT * FROM (VALUES (1, 'Balaji', NULL),

    (2, 'Siva', NULL),

    (3,'Balaji', 'Prasad'),

    (4,NULL,'Raj'),

    (5,NULL,NULL),

    (6,'Karthi','m')) as E(Sno,FirstName,MiddleName) )

Now query this to get comma separate names out of this

      

SELECT    

p.sno,p.firstname,p.middlename,

IsNull(p.FirstName, '')

+ CASE

 WHEN p.MiddleName = ''

 THEN ''

 WHEN (IsNull(p.FirstName, '') <> '' and IsNull(p.MiddleName, '') <> '')

 THEN IsNull(', ' + p.MiddleName, '')

 ELSE IsNull(p.MiddleName, '') END As Name

FROM  EmployeeCTE AS p

Now the output will come as comma separated if there values present in firstname or lastname

sno	firstname	middlename	Name
1	Balaji	NULL	Balaji
2	Siva	NULL	Siva
3	Balaji	Prasad	Balaji, Prasad
4	NULL	Raj	Raj
5	NULL	NULL
6	Karthi	m	Karthi, m