I have 2 tables TABLE1 and TABLE2 and joining these two tables, is TABLE3 which is my expected result. Have attached all the details in the screenshot. It will be really helpful if anyone can provide solutions to this. Thanks in advance. enter image description here
Sample data in lines #1 - 8; query that does the job begins at line #10.
SQL> with
2 table1 (categoryid, categoryname, categorydesc) as
3 (select 1, 'C1', 'Category 1' from dual),
4 table2 (identifier, categoryid, role, rank) as
5 (select 9001, 1, 'ZT01', 1 from dual union all
6 select 9002, 1, 'ZT01', 1 from dual union all
7 select 9002, 1, 'ZT04', -1 from dual
8 )
9 --
10 select a.categoryid,
11 a.categoryname,
12 a.categorydesc,
13 b.identifier,
14 listagg(b.role ||':'|| b.rank, ', ') within group (order by b.role) rolerank
15 from table1 a join table2 b on a.categoryid = b.categoryid
16 group by a.categoryid,
17 a.categoryname,
18 a.categorydesc,
19 b.identifier;
CATEGORYID CA CATEGORYDE IDENTIFIER ROLERANK
---------- -- ---------- ---------- --------------------
1 C1 Category 1 9001 ZT01:1
1 C1 Category 1 9002 ZT01:1, ZT04:-1
SQL>