I am not sure if this question is asked anywhere else before. I am not sure how to put it also. But I will explain with a scenario.
I have the following tables
TAB1 with columns : USERID, CODE, COUNTRY
TAB2 with columns : USERID, CODE, EMAIL
Example contents:
TAB1:
RISHI, A1B2C3, INDIA
RISHI, D2E3F4, INDIA
KANTA, G3H4I5, INDONESIA
TAB2:
RISHI, A1B2C3, rishi1@test.com
RISHI, A1B2C3, rishi2@test.com
RISHI, A1B2C3, rishi3@test.com
RISHI, D2E3F4, rishi1@test.com
RISHI, D2E3F4, rishi2@test.com
KANTA, G3H4I5, kanta1@test.com
What I want from a select query or pl/sql stored procedure is a result like this:
RISHI, INDIA, A1B2C3, (rishi1@test.com, rishi2@test.com, rishi3@test.com)
RISHI, INDIA, D2E3F4, (rishi1@test.com, rishi2@test.com)
If I do a select like :
select a.userid, a.code, a.country, b.email
from tab1.a, tab2.b
where a.userid = b.userid
and a.code = b.code
and a.userid = 'RISHI';
I get the result as :
RISHI, INDIA, A1B2C3, rishi1@test.com
RISHI, INDIA, A1B2C3, rishi2@test.com
RISHI, INDIA, A1B2C3, rishi3@test.com
RISHI, INDIA, D2E3F4, rishi1@test.com
RISHI, INDIA, D2E3F4, rishi2@test.com
What I basically need is the email ids grouped together into an array. Assume that TAB1 contains many more columns which I actually require but I have omitted in this example, but TAB2 has only these three columns.
select a.userid, a.code, a.country, listagg(b.email, ',') within group (order by b.email) as "Emails"
from tab1.a, tab2.b
where a.userid = b.userid
and a.code = b.code
and a.userid = 'RISHI'
group by a.userid, a.code, a.country;