create table role (
  role varchar(20),
  id int
);

insert into role (role, id) values ('Friend', 1);
insert into role (role, id) values ('Son', 2);
insert into role (role, id) values ('Daughter', 3);
insert into role (role, id) values ('Father', 4);
insert into role (role, id) values ('Mother', 5);
insert into role (role, id) values ('Brother', 6);
insert into role (role, id) values ('Sister', 7);

create table person (
  persons varchar(20),
  personid int
);

insert into person (persons, personid) values ('James', 1);
insert into person (persons, personid) values ('Peter', 2);
insert into person (persons, personid) values ('Joseph', 3);
insert into person (persons, personid) values ('Jeni', 4);

create table role_person (
  roleid int,
  personid int
);

insert into role_person (roleid, personid) values (2, 1);
insert into role_person (roleid, personid) values (2, 2);
insert into role_person (roleid, personid) values (4, 2);
insert into role_person (roleid, personid) values (6, 2);
insert into role_person (roleid, personid) values (6, 2);
insert into role_person (roleid, personid) values (3, 4);
insert into role_person (roleid, personid) values (4, 3);

我希望最终输出如下
最终输出

persons  friend  son  daughter  father  mother  brother  sister
    James    -       Y    -         -       -       -        -
    Peter    -       Y    -         Y       -       Y        -
    Joseph   -       -    -         Y       -       -        -
    Jeni     -       -    Y         -       -       -        -

由于底层数据库是mariadb，我不能使用pivot函数。请注意，人们可以增加或减少/也可以增加角色，如祖父、妻子等。
不知道如何在存储过程或查询中处理这个问题。
我们可以使用xml数据类型来处理动态查询。