SQLite 3.27

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

CREATE TABLE table1 (name varchar(20), role_id varchar(20), dep_id varchar(20));
CREATE TABLE table2 (name varchar(20), role_id varchar(20), ipv6 varchar(20), ipv4 varchar(20));
INSERT INTO table1 (name, role_id, dep_id) VALUES
('a','1','d1'),
('a','1','d2'),
('b','2','d3'),
('b','3','d5'),
('c','3','d4');
INSERT INTO table2 (name, role_id, ipv6, ipv4) VALUES
('a','1','ipv6_1','ipv4_1'),
('a','2','ipv6_2','ipv4_1'),
('b','7','ipv6_3','ipv4_2'),
('b','7','ipv6_7','ipv4_8'),
('e','2','ipv6_4','ipv4_3');
WITH
  common_names AS (SELECT name FROM table1 INTERSECT SELECT name FROM table2),
  cte AS (
    SELECT name, role_id, dep_id, null ipv6, null ipv4 FROM table1
    UNION ALL
    SELECT name, role_id, null, ipv6, ipv4 FROM table2
  )
SELECT name,
       GROUP_CONCAT(DISTINCT role_id) roles,
       GROUP_CONCAT(DISTINCT dep_id) deps,
       GROUP_CONCAT(DISTINCT ipv6) ipv6s,
       GROUP_CONCAT(DISTINCT ipv4) ipv4s
FROM cte
WHERE name IN common_names
GROUP BY name;


name
roles
deps
ipv6s
ipv4s


a
1,2
d1,d2
ipv6_1,ipv6_2
ipv4_1

b
2,3,7
d3,d5
ipv6_3,ipv6_7
ipv4_2,ipv4_8

name	roles	deps	ipv6s	ipv4s
a	1,2	d1,d2	ipv6_1,ipv6_2	ipv4_1
b	2,3,7	d3,d5	ipv6_3,ipv6_7	ipv4_2,ipv4_8