CODEWITHSUNDEEP
sqlsql-serversql-server-2016for-xml-pathstuff
Yashi Vaidehi Arya
Yashi Vaidehi Arya

Reputation: 1

How to return blank or null record when using STUFF and XML PATH on SQL Server 2016?

I am using STUFF and XML PATH as it is obligatory that I use SQL Server 2016. I have been working on this query and I cannot figure out how to return null or blank record when using STUFF and XML PATH. Below is the snippet of the query.

SELECT 
    Names = STUFF((SELECT '; ' + CONCAT(t3.FirstName, ' ', t3.LastName)
                   FROM table1 t1
                   LEFT JOIN table2 t2 ON t0.columnID = t2.columnID
                   LEFT JOIN table3 t3 ON t3.columnID = t3.columnID
                   FOR XML PATH('')), 1, 1,''),

The issue is that when there is no lastname/firstname, this query returns the separators '; ;' but I need it to return blank value if there is no lastname / firstname.

Upvotes: 0

Views: 644

Answers (1)

Charlieface
Charlieface

Reputation: 72298

Just add a WHERE:

WHERE t3.FirstName IS NOT NULL OR t3.LastName IS NOT NULL

In this case, you probably also want to deal with one of them being null and getting an extra space:

SELECT '; ' + CASE
    WHEN t3.FirstName IS NOT NULL AND t3.LastName IS NULL THEN t3.FirstName
    WHEN t3.LastName IS NOT NULL AND t3.FirstName IS NULL THEN t3.LastName
    ELSE CONCAT(t3.FirstName, ' ', t3.LastName) END

Also, because your separator is two characters long, you need to change the STUFF parameters:
FOR XML PATH('')), 1, 2,'')

Upvotes: 1

Related Questions