I have the table
COSAS
CAMPOA CAMPOB
Frutas Manzana
Frutas Peras
Frutas Uvas
Lacteos Yogur
CANTIDAD
CAMPOC CAMPOD
Frutas 1
Manzana 2
Peras 3
Yogur 1
Lacteos 1
I need a query that ponsiga this:
FIELD A and FIELD C In such a way that it would be
Frutas 1,2,3
Lacteos 1,1
Obviously, to achieve your result, it is necessary to make a join between the 2 tables. The weirdness of your question is that CANTIDAD.CAMPOC contains values of both columns of the COSAS table. That is, COSAS.CAMPOA fulfills a double function of categorizing the CAMPOB but also making a join with the table CANTIDAD . Again, that design seems strange to me.
In any case, what you ask is possible, but first you have to correct the design in a virtual way, making all the values in the COSAS table that you need to make a join remain in the same column. This is achieved with the following query:
select distinct a as categoria,
a as elemento
from cosas
union all
select a as categoria,
b as elemento
from cosas
Afterwards it's easy to add the join and the LISTAGG :
with cte as (
select distinct a as categoria,
a as elemento
from cosas
union all
select a as categoria,
b as elemento
from cosas
)
select t.categoria,
listagg(c.d, ',') within group (order by c.d) as lista_cantidades
from cte t
join cantidad c
on c.c = t.elemento
group by t.categoria
Setup
create table cosas (
a varchar2(50) not null,
b varchar2(50) not null
)
/
create table cantidad (
c varchar2(50) not null,
d number(5) not null
)
/
insert all
into cosas (a,b) values ('Frutas', 'Manzana')
into cosas (a,b) values ('Frutas', 'Peras')
into cosas (a,b) values ('Frutas', 'Uvas')
into cosas (a,b) values ('Lacteos', 'Yogur')
select null from dual
/
insert all
into cantidad (c,d) values ('Frutas', 1)
into cantidad (c,d) values ('Manzana', 2)
into cantidad (c,d) values ('Peras', 3)
into cantidad (c,d) values ('Yogur', 1)
into cantidad (c,d) values ('Lacteos', 1)
select null from dual
/