Oracle SQL: How to remove duplicate in listagg

Question

After using listagg to combine my data, there are many duplicates that I want to remove.

Original Table

There will be only 3 types of technologies in total with no specific pattern in my data. I am wondering is it possible to remove all the duplicates and only keep 1 type in the respective row?

    select
    NAME,
    RTRIM(
        REGEXP_REPLACE(
            (LISTAGG(
                NVL2(Membrane_column, 'Membrane, ', NULL)
                || NVL2(SNR_column, 'SNR, ', NULL)
                || NVL2(SMR_column, 'SMR, ', NULL)
                ) within group (ORDER BY name)),
        'Membrane, |SNR, |SMR, ', '', '1', '1', 'c')
    ', ')
    as TECHNOLOGY
    from
    Table A

The current table I have for now

Name	Technology
A	SNR, SMR, SMR, SNR
B	Membrane, SNR, SMR, Membrane
C	SMR, SMR, Membrane

Desired Table

Name	Technology
A	SNR, SMR
B	Membrane, SNR, SMR
C	SMR, Membrane

Answer 1

Maybe just create the SUM of the SNR/SMR/Membrane columns, group them by name, and replace the numbers with the strings that you want to see in the output.

Query (first step ...)

select name
, sum( snr_column ), sum( smr_column ), sum( membrane_column ) 
from original 
group by name
;

-- output
NAME    SUM(SNR_COLUMN)     SUM(SMR_COLUMN)     SUM(MEMBRANE_COLUMN)
2       1                   1                   2
3       null                2                   1
1       2                   2                   null    

Replace the sums, concatenate, remove the trailing comma with RTRIM()

select 
  name 
, rtrim( 
     case when sum( snr_column ) >= 1 then 'SNR, ' end
  || case when sum( smr_column ) >= 1 then 'SMR, ' end 
  || case when sum( membrane_column ) >= 1 then 'Membrane' end 
  , ' ,'
  ) as technology
from original 
group by name
order by name
;
-- output
NAME    TECHNOLOGY
1       SNR, SMR
2       SNR, SMR, Membrane
3       SMR, Membrane

Code the CASEs in the required order. DBfiddle