Please help, I have a small query that returns multiple elements in a LISTAGG function. I have tried with using "listagg (DISTINCT column,', ' on overflow truncate with count) function but received an error. I will be grateful for your help.
Thanks
This is the current output showing duplicates
I want the query to return
My Query
select pamcod, listagg(PAMIUN, ' & ' ) within group (order by PAMIUN)
from proam
where pamsoc = 80 and pamrou = 14 and pamcod = 556 group by pamcod order by pamcod
-- Create table
create table PROAM
(
pamsoc NUMBER(2) not null,
pamrou NUMBER(2) not null,
pamcod NUMBER(4) not null,
pamlpr NUMBER(2),
pamstr NUMBER(2),
pamfam NUMBER(2),
pammod NUMBER(8),
pampsc NUMBER(5,2),
pamiun NUMBER(11,2),
pampne NUMBER(11,2),
pammar NUMBER(2)
);
Insert into PROAM (PAMSOC,PAMROU,PAMCOD,PAMLPR,PAMSTR,PAMFAM,PAMMOD,PAMPSC,PAMIUN,PAMPNE,PAMMAR) values (80,14,556,10,null,null,null,null,15.75,null,1);
Insert into PROAM (PAMSOC,PAMROU,PAMCOD,PAMLPR,PAMSTR,PAMFAM,PAMMOD,PAMPSC,PAMIUN,PAMPNE,PAMMAR) values (80,14,556,20,null,null,null,null,15.75,null,1);
Insert into PROAM (PAMSOC,PAMROU,PAMCOD,PAMLPR,PAMSTR,PAMFAM,PAMMOD,PAMPSC,PAMIUN,PAMPNE,PAMMAR) values (80,14,556,40,null,null,null,null,7.75,null,1);
Insert into PROAM (PAMSOC,PAMROU,PAMCOD,PAMLPR,PAMSTR,PAMFAM,PAMMOD,PAMPSC,PAMIUN,PAMPNE,PAMMAR) values (80,14,556,70,null,null,null,null,7.75,null,1);
Insert into PROAM (PAMSOC,PAMROU,PAMCOD,PAMLPR,PAMSTR,PAMFAM,PAMMOD,PAMPSC,PAMIUN,PAMPNE,PAMMAR) values (80,14,556,10,null,null,null,null,15.75,null,4);
Insert into PROAM (PAMSOC,PAMROU,PAMCOD,PAMLPR,PAMSTR,PAMFAM,PAMMOD,PAMPSC,PAMIUN,PAMPNE,PAMMAR) values (80,14,556,20,null,null,null,null,15.75,null,4);
Insert into PROAM (PAMSOC,PAMROU,PAMCOD,PAMLPR,PAMSTR,PAMFAM,PAMMOD,PAMPSC,PAMIUN,PAMPNE,PAMMAR) values (80,14,556,40,null,null,null,null,7.75,null,4);
Insert into PROAM (PAMSOC,PAMROU,PAMCOD,PAMLPR,PAMSTR,PAMFAM,PAMMOD,PAMPSC,PAMIUN,PAMPNE,PAMMAR) values (80,14,556,70,null,null,null,null,7.75,null,4);