Concatenate with string but exclude when null

Oracle v11

Exclude a string in a Concat when the value is NULL?

Work Query:

Select 
 *
 LastName || ',' || FirstName AS Last_First_Name

 --I've tried using NVL & NVL2
 --NVL(LastName || ',' || FirstName,'') AS Last_First_Name2
 FROM TableA

TableA

LastName   FirstName  ID
 Smith       Ann      1
                      2

Output I’m getting

LastName FirstName ID   LastName_FirstName
 Smith       Ann    1    Smith,Ann
                    2    ,

Expected Output:

LastName FirstName ID   LastName_FirstName
 Smith       Ann    1    Smith,Ann
                    2      

Answer

Something like this… I added simulated inputs to test all four possibilities. However, note that if you may have last name but no first name, and also first name but no last name, the combined column will show just one name but you will not know if it is first or last. (Also, when a comma is added, I also add a space after the comma, as is usual; that can be controlled easily though.)

with
  tablea ( lastname, firstname, id ) as (
               select 'Smith', 'Ann'  , 1 from dual
     union all select null   , null   , 2 from dual
     union all select 'Ang'  , null   , 3 from dual
     union all select null   , 'Murat', 4 from dual
  )
-- End of simulated inputs (for testing only, not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select lastname, firstname, id,
       lastname 
         || case when lastname is not null and firstname is not null then ', ' end
         || firstname
       as last_first
from   tablea
;



LASTNAME   FIRSTNAME          ID LAST_FIRST 
---------- ---------- ---------- ------------
Smith      Ann                 1 Smith, Ann  
                               2             
Ang                            3 Ang         
           Murat               4 Murat