SQL Server 2014

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

create table pn(
  id int identity(1,1) primary key,
  proj_id int not null,
  pn varchar(10) not null,
  mtyp_id int not null);
insert into pn (proj_id, pn, mtyp_id)
values (1, 'Cc',1), (1, 'Bb',2), (1, 'Aa',3), (2, 'Dd',2), (3, 'Ee',1), (3, 'Ff',3);
6 rows affected

create table mscht(
id_mtyp int identity(1,1) primary key,
masch varchar(10) not null);
insert into mscht (masch)
values ('WSG'), ('HSG'), ('GSF'), ('WIP');
4 rows affected

create table docmap(
ID_DM int identity(1,1) primary key,
DN_ID int not null,
PN_ID int not null);
insert into docmap (DN_ID, PN_ID)
values (1, 1), (1,2), (2,3), (3,4), (4,5), (5,6);
6 rows affected

select DN_ID, 
       stuff( ( select distinct char(13) + char(10) + 'PN ' + pn + ' (', masch, ') '
                from   pn inner join mscht on id_mtyp = id_mtyp inner join docmap on id = mtyp_id
                where  proj_id = t.proj_id
                order by 1 for xml path(''), type).value('.', 'varchar( max )' ), 1, 2, '' ) as zeile
from   pn t inner join docmap on t.id = PN_ID


DN_ID
zeile


1
PN Aa (GSF) 
PN Aa (HSG) 
PN Aa (WIP) 
PN Aa (WSG) 
PN Bb (GSF) 
PN Bb (HSG) 
PN Bb (WIP) 
PN Bb (WSG) 
PN Cc (GSF) 
PN Cc (HSG) 
PN Cc (WIP) 
PN Cc (WSG)

1
PN Aa (GSF) 
PN Aa (HSG) 
PN Aa (WIP) 
PN Aa (WSG) 
PN Bb (GSF) 
PN Bb (HSG) 
PN Bb (WIP) 
PN Bb (WSG) 
PN Cc (GSF) 
PN Cc (HSG) 
PN Cc (WIP) 
PN Cc (WSG)

2
PN Aa (GSF) 
PN Aa (HSG) 
PN Aa (WIP) 
PN Aa (WSG) 
PN Bb (GSF) 
PN Bb (HSG) 
PN Bb (WIP) 
PN Bb (WSG) 
PN Cc (GSF) 
PN Cc (HSG) 
PN Cc (WIP) 
PN Cc (WSG)

3
null

4
null

5
null

DN_ID	zeile
1	PN Aa (GSF) PN Aa (HSG) PN Aa (WIP) PN Aa (WSG) PN Bb (GSF) PN Bb (HSG) PN Bb (WIP) PN Bb (WSG) PN Cc (GSF) PN Cc (HSG) PN Cc (WIP) PN Cc (WSG)
1	PN Aa (GSF) PN Aa (HSG) PN Aa (WIP) PN Aa (WSG) PN Bb (GSF) PN Bb (HSG) PN Bb (WIP) PN Bb (WSG) PN Cc (GSF) PN Cc (HSG) PN Cc (WIP) PN Cc (WSG)
2	PN Aa (GSF) PN Aa (HSG) PN Aa (WIP) PN Aa (WSG) PN Bb (GSF) PN Bb (HSG) PN Bb (WIP) PN Bb (WSG) PN Cc (GSF) PN Cc (HSG) PN Cc (WIP) PN Cc (WSG)
3	null
4	null
5	null