Comma-delimit multiple columns when some values may be null

I need to comma-delimit the values contained in multiple columns, when each of the columns could contain a NULL value:

with

data as (

  select  null a, null b, null c from dual
  union all

  select  'a' a, null b, null c from dual
  union all
  select  null a, 'b' b, null c from dual
  union all
  select  null a, null b, 'c' c from dual

  union all
  select  'a' a, 'b' b, null c from dual
  union all
  select  'a' a, null b, 'c' c from dual

  union all
  select  null a, 'b' b, 'c' c from dual

  union all
  select  'a' a, 'b' b, 'c' c from dual

)

select  a,b,c
        ,case
        when a is not null and b is not null and c is not null then a || ',' || b || ',' ||c
        when a is not null and b is not null then a || ',' || b
        when a is not null and c is not null then a || ',' || c
        when b is not null and c is not null then b || ',' || c
        when a is not null then a
        when b is not null then b
        when c is not null then c
        end abc
from    data

Results:

A   B   C   ABC

a           a
    b       b
        c   c
a   b       a,b
a       c   a,c
    b   c   b,c
a   b   c   a,b,c

Is there an approach that is less cumbersome than what I’ve done for abc, preferably without having to use PL/SQL?

Answer

The function that you really want is concat_ws(), but Oracle doesn’t have one. But here is an alternative that is a bit less cumbersome than your method:

select a, b, c,
       trim(both ',' from replace(replace(a || ',' || b || ',' || c, ',,', ','), ',,', ',')
from data;

This creates the string with the commas. Oracle ignores the NULL values in a string concatenation. It then removes duplicate commas and leading and trailing commas.

Another method is:

select a, b, c,
       trim(trailing ',' from
            coalesce(a || ',', '') || coalesce(b || ',', '') || coalesce(c || ',', '') 
           )
from data;

I actually like this version better. It is more generalizable; the intermediate result doesn’t have a bunch of comma gumming up the middle of the string.