Separate columns by commas ignoring nulls

I have the below table:

A	B	C	D	E
A1	null	C1	null	E1
A2	B2	C2	null	null
null	null	C3	null	E3

I would like the below output (separated by commas, if any value is null, then do not add a comma):

F
A1, C1, E1
A2, B2, C2
C3, E3

Answer

You can concatenate all columns regardless of whether they are null or not (saving a lot of comparisons to null), but then fix the commas with string functions. Whether this will be faster or slower than checking each value individually for being null will depend on the data (how many columns – I assume 5 is just for illustration – and how frequent null is in the data, for example).

I included more data for testing in the with clause (which, obviously, is not part of the answer; remove it and use your actual table and column names).

with
  inputs (a, b, c, d, e) as (
    select 'A1', null, 'C1', null, 'E1' from dual union all
    select 'A2', 'B2', 'C2', null, null from dual union all
    select null, null, 'C3', null, 'E3' from dual union all
    select null, null, null, null, null from dual union all
    select null, 'B5', null, null, null from dual
  )
select a, b, c, d, e,
       regexp_replace(
         trim (both ',' from a || ',' || b || ',' || c || ',' || d || ',' || e)
         , ',+', ', ') as f
from   inputs;

A    B    C    D    E    F              
---- ---- ---- ---- ---- ---------------
A1        C1        E1   A1, C1, E1     
A2   B2   C2             A2, B2, C2     
          C3        E3   C3, E3         
                                        
     B5                  B5             
    

EDIT

In a comment, the OP expanded the scope of the question. The new requirement is to also remove leading and/or trailing whitespace from the input tokens (including ignoring tokens altogether, if they consist entirely of whitespace).

This can be achieved as follows:

select a, b, c, d, e,
       ltrim(
         rtrim(
           regexp_replace(
             a || ',' || b || ',' || c || ',' || d || ',' || e
             , '[[:space:]]*,[,[:space:]]*', ', '
           )
           , ', ' || chr(9)
         )
         , ', ' || chr(9)
       ) as f
from   inputs;