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;
