I am looking to write a SQL that will return me data in specific format. This is the query.

SELECT F_NAME, M_NAME, L_NAME 
WHERE PERSON_DEPT = 'HR'

I want the result in this format:

FirstName:Scott,MiddleName:Rotham,LastName:Tiger

If there is no middle name, it should be:

FirstName:Scott,LastName:Tiger

If there is no first name, it should be:

MiddleName:Rotham,LastName:Tiger

If there is no last name, it should be:

FirstName:Scott,MiddleName:Rotham

If all three columns are empty, it should be nothing.

I am trying to do something like below, but I need null (empty) check for M_NAME and L_NAME as well.

SELECT (CASE
   WHEN F_NAME IS NOT NULL          
   THEN CONCAT(F_NAME,M_NAME,L_NAME)
   ELSE '' 
END) AS EMPLOYEE
FROM PERSON
WHERE PERSON_DEPT = 'HR'

CodePudding user response：

You can use concat_ws which ignores nulls instead of concat which doesn't. Larnu has a good solution if your DB version doesn't support this.

concat_ws(',', f_name, m_name, l_name)

CodePudding user response：

On a recent version of SQL Server (2017 ) you can use CONCAT_WS to avoid NULL concatenation and add the delimiter, but also make use of said NULL concatenation with the concatenation operator:

SELECT CONCAT_WS(',',N'FirstName:'   F_NAME, N'MiddleName:'   M_NAME, N'LastName:'   L_NAME)
FROM dbo.YourTable;

If you're on an older version of SQL Server, however, then you can use the older method of prefixing all the values with the delimiter and using STUFF to remove the first one:

SELECT STUFF(CONCAT(N',FirstName:'   F_NAME, N',MiddleName:'   M_NAME, N',LastName:'   L_NAME),1,1,'')
FROM dbo.YourTable;

CodePudding user response：

One option would be

select 
case when f_name is not null and m_name is not null and l_name is not null 
     then 'Firstname:'   f_name   ','   'MiddleName:'   m_name   ','   'LastName:'   l_name 
     when f_name is not null and m_name is null and l_name is not null 
     then 'Firstname:'   f_name   ','   'LastName:'   l_name  
     when f_name is null and m_name is not null and l_name is null 
     then 'Middlename:'   m_name   ','   'MiddleName:'   m_name
     when f_name is null and m_name is null and l_name is null 
     then null 
else null end result
    from t

You might extend the logic in any way you want.

Demo

db<>fiddle

CodePudding user response：

This should work, including correct handling of commas if any of the fields are empty.

DROP TABLE IF EXISTS #PERSON_DEPT
CREATE TABLE #PERSON_DEPT(F_NAME VARCHAR(10), M_NAME VARCHAR(10), L_NAME VARCHAR(10))

INSERT INTO #PERSON_DEPT (F_NAME, M_NAME, L_NAME) VALUES 
('Max', 'V', 'O'),
('John', null, 'Doe'),
('Alex', 'D', null),
('Spencer', null, null),
(null, null, null)

SELECT 
    COALESCE('FirstName:'   F_NAME, '')   (CASE WHEN M_NAME IS NULL THEN '' ELSE ',' END)   
    COALESCE('MiddleName:'   M_NAME, '')   (CASE WHEN L_NAME IS NULL THEN '' ELSE ',' END)   
    COALESCE('LastName:'   L_NAME, '') 
FROM #PERSON_DEPT

The result: