sql - TSQL - extend query consisting of cross apply and pivot -

this question based on previous question. need extend query can incorporate 2 other tables (running on server instance).

in fiddle added 2 tables:

create table lookup  ([docid] varchar(10), [doctype] varchar(100), [partnumber] varchar(100), [internalid] varchar(100)); insert lookup  ([docid],[doctype],[partnumber], [internalid]) values   ('d0305415', 'doctypesub', 'x0455', null),   ('d0157632', 'doctypemain', null, 'xy05570-xy05571'),   ('d0181511', 'doctypemain',null, 'xy05572-xy05573'),   ('d0157633', 'doctypemain', null, 'xy06380-xy06381'),   ('d0156037', 'doctypesub', 'x0326', null),   ('d0151874', 'doctypemain', null, 'xy05345');  create table links   ([docidtop] varchar(10), [docidbottom] varchar(10)); insert links   ([docidtop],[docidbottom]) values   ('d0157632', 'd0305415'),   ('d0181511', 'd0305415'),   ('d0157633', 'd0305415'),   ('d0151874', 'd0156037'); 

regarding output need display new internalid column in comma-separated column based on partnumber column.

this query outputs correct data:

 select c.doctype c_doctypesub, c.docid c_docid, c.partnumber c_partnumber ,   b.docidtop b_docidtop, b.docidbottom b_docidbottom,  a.*   lookup a, links b, lookup c  a.doctype = 'doctypemain'   , a.docid = b.docidtop , b.docidbottom = c.docid   , c.doctype = 'doctypesub'  ; 

my problem put pieces can internalid show in old query below:

---------------- -- old query --  ---------------- cte_no_nums ( select  docid,         case             when patindex('%[0-9]%',column1) > 0                 substring(column1,0,patindex('%[0-9]%',column1))             else column1         end cols,         coalesce(column2,column3) vals miscvalues       column2 not null         or  column3 not null ), cte_pivot         (     select docid,partnumber,prio,[length],material     cte_no_nums     pivot     (         max(vals) cols in (partnumber,prio,[length],material)     ) pvt )  select  a.docid + ' # ' + b.vals [docid # plant],         a.docid,         a.partnumber,         a.prio,         b.vals plant,         a.partnumber + '#' + a.material + '#' + a.[length] identification,         a.[length],         substring(ca.colors,0,len(ca.colors)) colors --substring removes last comma cte_pivot inner join cte_no_nums b     on      a.docid = b.docid         , b.cols = 'plant' cross apply (   select vals + ','                  cte_no_nums c                    cols = 'color'                      , c.docid = a.docid                  xml path('')              ) ca(colors)             ; 

hope can show me how can achieved. if unclear feel free ask. , no, i'm not in charge of data structure :-)

thank you.

based on @shnugo answer try not unoptimized (yet) answer, think @shnugo doesn´t check link table, because don't need change data information:

[...ctes before...]     select  a.docid + ' # ' + b.vals [docid # plant],     a.docid,     a.partnumber,     a.prio,     b.vals plant,     a.partnumber + '#' + a.material + '#' + a.[length] identification,     a.[length],     substring(ca.colors,0,len(ca.colors)) colors, --substring removes last comma     stuff((select ', ' + x.internalid     lookup x     inner join links z     on x.docid = z.docidtop     inner join lookup x2     on x2.docid = z.docidbottom     x2.partnumber=a.partnumber     xml path(''),type).value('.','nvarchar(max)'),1,2,'') concatinternalid [...from...]