SQL Server 2019

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

create table Persons (id int, Name varchar(50), BankCode int)
create table Banks (id int, Name varchar(50), BankCode int)

insert into Persons (id, Name, BankCode)
values (1, 'Jack', 2), (2, 'Jane', 2), (3, 'John', 5)

insert into Banks (id, Name, BankCode)
values (1, 'National', 2), (2, 'National Subsidiary', 2),
(3, 'GNB', 3), (4, 'Global Banking', 5)

select * from Persons
select * from Banks


id
Name
BankCode


1
Jack
2

2
Jane
2

3
John
5




id
Name
BankCode


1
National
2

2
National Subsidiary
2

3
GNB
3

4
Global Banking
5


select p.id,
       p.Name,
       ( select string_agg(b.Name, ', ')
         from   Banks b
         where  b.BankCode = p.BankCode
       ) as Banks
from   Persons p



id
Name
Banks


1
Jack
National, National Subsidiary

2
Jane
National, National Subsidiary

3
John
Global Banking


drop table Persons
drop table Banks

id	Name	Banks
1	Jack	National, National Subsidiary
2	Jane	National, National Subsidiary
3	John	Global Banking