Tuesday, August 7, 2018

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

No comments:

Post a Comment