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);

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');

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);

create view v as -- damit die Übersicht nicht verloren geht select dn_id, cast('PN ' + pn + '(' + masch + ') ' as nvarchar) as zeile from docmap d inner join pn p on d.pn_id = p.id inner join mscht m on p.mtyp_id = m.id_mtyp;

with x as ( select dn_id, zeile, -- Zeilen nach dn_id partitioniert durchnumerieren row_number() over (partition by dn_id order by dn_id) as rnum, -- Anzahl der Zeilen ermittel und merken count(*) over (partition by dn_id) as cnt from v), y as ( -- Rekursion über alle Zeilen select dn_id, cast(x.zeile as nvarchar) as ganzezeile, zeile, rnum, cnt from x where rnum = 1 union all select y.dn_id, cast(x.zeile + char(13) + char(10) + y.ganzezeile as nvarchar), x.zeile, x.rnum, x.cnt from x inner join y on x.dn_id = y.dn_id and x.rnum = y.rnum + 1) select dn_id, ganzezeile from y where rnum = cnt order by 1;