FirstName, LastName in SQL, too complex?

Question

This SQL seems complex, is there an easier way to get FirstName, LastName when one or both of the fields can be NULL?

SELECT COALESCE(LastName,'')+
       CASE WHEN LastName+FirstName IS NOT NULL THEN ', ' END+
       COALESCE(FirstName,'') AS Name
FROM Person

Answer 1

How about

SELECT COALESCE(LastName + ', ' + FirstName, 
                LastName, FirstName) Name
FROM Person

if firstname or lastname is null the entire first expression (with the ,), becomes null, forcing the coalesce to examine, second, the lastname alone, and then if lastname is null, finally, the firstname alone.