After using listagg to combine my data, there are many duplicates that I want to remove.
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 |
CodePudding user response:
This could be an easy way:
select name, listagg(technology, ', ') within group (order by 1) -- or whatever order you need
from
(
select distinct name, technology
from tableA
)
group by name
CodePudding user response:
Starting from Oracle 19c listagg
supports distinct
keyword. Also within group
became optional.
with a as (
select column_value as a
from table(sys.odcivarchar2list('A', 'B', 'A', 'B', 'C')) q
)
select listagg(distinct a, ',')
from a
LISTAGG(DISTINCTA,',')
----------------------
A,B,C
livesql example here.
CodePudding user response:
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