Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE Table1 (Num, Code, Developer, Brand, Model, Article) AS
SELECT '111/111/111', '0803', 'Dev1', 'Bra1', 'Mod1', 'Art1' FROM DUAL UNION ALL
SELECT '222/222/222', '0706', 'Dev2', 'Bra2', 'Mod2', 'Art2' FROM DUAL UNION ALL
SELECT '222/222/222', '0706', 'Dev2', 'Bra2', 'Mod2', 'Art3' FROM DUAL UNION ALL
SELECT '222/222/222', '0706', 'Dev2', 'Bra2', 'Mod3', 'Art5' FROM DUAL UNION ALL
SELECT '333/333/333', '0717', 'Dev3', 'Bra3', 'Mod4', 'Art4' FROM DUAL UNION ALL
SELECT '333/333/333', '0717', 'Dev3', 'Bra3', 'Mod4', 'Art6' FROM DUAL UNION ALL
SELECT '444/444/444', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art1' FROM DUAL UNION ALL
SELECT '444/444/444', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art7' FROM DUAL;
8 rows affected

CREATE TABLE Table2 (Num, Code, Developer, Brand, Model, Article) AS
SELECT '666/666/666', '0803', 'Dev1', 'Bra1', 'Mod1', 'Art1' FROM DUAL UNION ALL
SELECT '777/777/777', '0706', 'Dev2', 'Bra2', 'Mod7', 'Art7' FROM DUAL UNION ALL
SELECT '777/777/777', '0706', 'Dev2', 'Bra2', 'Mod7', 'Art7' FROM DUAL UNION ALL
SELECT '888/888/888', '0717', 'Dev3', 'Bra2', 'Mod4', 'Art4' FROM DUAL UNION ALL
SELECT '888/888/888', '0717', 'Dev3', 'Bra3', 'Mod4', 'Art4' FROM DUAL UNION ALL
SELECT '888/888/888', '0717', 'Dev3', 'Bra3', 'Mod8', 'Art8' FROM DUAL UNION ALL
SELECT '999/999/999', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art1' FROM DUAL UNION ALL
SELECT '999/999/999', '0805', 'Dev1', 'Bra1', 'Mod1', 'Art7' FROM DUAL;
8 rows affected

SELECT MAX(CASE tbl WHEN 1 THEN num END) AS num_tab1,
       MAX(CASE tbl WHEN 2 THEN num END) AS num_tab2,
       code,
       developer,
       LISTAGG(DISTINCT CASE tbl WHEN 1 THEN model END, ';')
         WITHIN GROUP (ORDER BY tbl, model) AS model_tab1,
       LISTAGG(DISTINCT CASE tbl WHEN 2 THEN model END, ';')
         WITHIN GROUP (ORDER BY tbl, model) AS model_tab2,
       LISTAGG(DISTINCT CASE tbl WHEN 1 THEN article END, ';')
         WITHIN GROUP (ORDER BY tbl, model) AS article_tab1,
       LISTAGG(DISTINCT CASE tbl WHEN 2 THEN article END, ';')
         WITHIN GROUP (ORDER BY tbl, model) AS article_tab2,
       COUNT(DISTINCT CASE model_tab_count WHEN 2 THEN model END) AS mod_count,
       COUNT(DISTINCT CASE article_tab_count WHEN 2 THEN article END) AS art_count
FROM   (
  SELECT t.*,
         COUNT(DISTINCT tbl) OVER (PARTITION BY code, developer, model)
           AS model_tab_count,
         COUNT(DISTINCT tbl) OVER (PARTITION BY code, developer, article)
           AS article_tab_count
  FROM   (
    SELECT 1 AS tbl, t.*
    FROM   table1 t
  UNION ALL
    SELECT 2 AS tbl, t.*
    FROM   table2 t
  ) t
)
GROUP BY code, developer


NUM_TAB1
NUM_TAB2
CODE
DEVELOPER
MODEL_TAB1
MODEL_TAB2
ARTICLE_TAB1
ARTICLE_TAB2
MOD_COUNT
ART_COUNT


222/222/222
777/777/777
0706
Dev2
Mod2;Mod3
Mod7
Art2;Art3;Art5
Art7
0
0

333/333/333
888/888/888
0717
Dev3
Mod4
Mod4;Mod8
Art4;Art6
Art4;Art8
1
1

111/111/111
666/666/666
0803
Dev1
Mod1
Mod1
Art1
Art1
1
1

444/444/444
999/999/999
0805
Dev1
Mod1
Mod1
Art1;Art7
Art1;Art7
1
2

NUM_TAB1	NUM_TAB2	CODE	DEVELOPER	MODEL_TAB1	MODEL_TAB2	ARTICLE_TAB1	ARTICLE_TAB2	MOD_COUNT	ART_COUNT
222/222/222	777/777/777	0706	Dev2	Mod2;Mod3	Mod7	Art2;Art3;Art5	Art7	0	0
333/333/333	888/888/888	0717	Dev3	Mod4	Mod4;Mod8	Art4;Art6	Art4;Art8	1	1
111/111/111	666/666/666	0803	Dev1	Mod1	Mod1	Art1	Art1	1	1
444/444/444	999/999/999	0805	Dev1	Mod1	Mod1	Art1;Art7	Art1;Art7	1	2