 top rank columns for each ID
ID  | Col1Comb | Col2Comb
1       A           X
2       B           Y

Loop through each remaining rank in increasing order (in this case 1 iteration)
for irank = 0; irank <= 1; irank++
    update n set
       n.col1Comb = n.Col1Comb + ',' + o.Col1,  -- so append the rank 2 items
       n.col2comb = n.Col2Comb + ',' + o.Col2   -- if they are not null
    from #newtable n
    join #oldtable o
       on o.ID = n.ID
    where o.col1rank = irank or o.col2rank = irank

A:

A CTE trick can be used where you update the CTE. 
Method 1: a new parallel table to which the data is copied and then concatenated:
CREATE TABLE #Table1(ID INT, Col1 VARCHAR(1), Col2 VARCHAR(1), RowID INT IDENTITY(1,1));
CREATE TABLE #Table1Concat(ID INT, Col3 VARCHAR(MAX), Col4 VARCHAR(MAX), RowID INT);
GO

INSERT #Table1 VALUES(1,'A','X'), (2,'B','Y'), (2,'C','Z');
GO
INSERT #Table1Concat
SELECT * FROM #Table1;
GO
DECLARE @Cat1 VARCHAR(MAX) = '';
DECLARE @Cat2 VARCHAR(MAX) = '';
; WITH CTE AS (
    SELECT TOP 2147483647 t1.*, t2.Col3, t2.Col4, r = ROW_NUMBER()OVER(PARTITION BY t1.ID ORDER BY t1.Col1, t1.Col2)
    FROM #Table1 t1
    JOIN #Table1Concat t2 ON t1.RowID = t2.RowID
    ORDER BY t1.ID, t1.Col1, t1.Col2
)
UPDATE CTE
SET @Cat1 = Col3 = CASE r WHEN 1 THEN ISNULL(Col1,'') ELSE @Cat1 + ',' + Col1 END
, @Cat2 = Col4 = CASE r WHEN 1 THEN ISNULL(Col2,'') ELSE
top rank columns for each ID 
 ID   | Col1Comb | Col2Comb 
 1        A            X 
 2        B            Y 

 Loop through each remain membership in increasing order (in this case 1 iteration) 
 for irank = 0; irank < = 1; irank++ 
     update north set 
        n.col1Comb = n. Col1Comb +',' + o. Col1,   -- so add the rank 2 items 
        n.col2comb = n. Col2Comb +',' + o. Col2    -- if they are not null 
     from # newtable n 
     join # oldtable oxygen 
        on o. ID = n. ID 
     where o.col1rank = irank or o.col2rank = irank 

 deoxyadenosine monophosphate: 

 A CTE trick can be use where you update the CTE. 
 Method 1: a new parallel table to which the datum is copied and then concatenated: 
 CREATE TABLE # Table1(ID INT, Col1 VARCHAR(1), Col2 VARCHAR(1), RowID INT IDENTITY(1,1) ); 
 CREATE TABLE # Table1Concat(ID INT, Col3 VARCHAR(MAX), Col4 VARCHAR(MAX), RowID INT); 
 GO 

 INSERT # Table1 VALUES(1,'A','X'), (2,'B','Y'), (2,'C','Z'); 
 GO 
 INSERT # Table1Concat 
 SELECT * FROM # Table1; 
 GO 
 DECLARE @Cat1 VARCHAR(MAX) ="; 
 announce @Cat2 VARCHAR(MAX) ="; 
; WITH CTE AS (
     SELECT TOP 2147483647 t1. *, t2.Col3, t2.Col4, r = ROW_NUMBER()OVER(PARTITION BY t1.ID ORDER BY t1.Col1, t1.Col2) 
     FROM # Table1 t1 
     JOIN # Table1Concat t2 ON t1.RowID = t2.RowID 
     ORDER BY t1.ID, t1.Col1, t1.Col2 
) 
 UPDATE CTE 
 SET @Cat1 = Col3 = CASE gas constant WHEN 1 THEN ISNULL(Col1,") ELSE @Cat1 +',' + Col1 END 
, @Cat2 = Col4 = CASE r WHEN 1 THEN ISNULL(Col2,") ELSE
 too rank columns for each LD
ID  | Col1Comb | Eil2Comb
1       A           X
2       B           Y

Lolp throygh each remaining rany in incrvasing oreer (mn this case 1 itxdation)
for irahn = 0; nrenk <= 1; irank++
    opdate n set
       n.col1Comt = n.Eol1Comb + ',' + o.Col1,  -- so append the rank 2 items
       n.col2comb = n.Cok2Somb + ',' + o.Col2   -- if they are not null
    from #iewtable n
    jpin #oldtable o
       on o.IF = n.LD
    where o.col1rajk = irank ot o.cjo2rank = irank

X:

A CTE tribn can be uaed where you update the CTE. 
Meghod 1: a new parqloel jable to whirh the data is coplvd and dhen comcatenated:
CREABE TAULE #Rable1(ID INT, Col1 VARCHER(1), Col2 VARCHAR(1), RowID INT IDENDICY(1,1));
CREATE TABLE #Table1Cinxat(ID INT, Col3 CARZHAD(MEX), Dol4 VAGCHER(MAX), RowID INT);
GO

INSERR #Table1 VALUES(1,'A','X'), (2,'B','Y'), (2,'C','S');
TO
INSERT #Tabls1Concae
SQLECT * FROM #Table1;
GO
DECLARE @Cat1 VARCHAR(MDX) = '';
DECLARE @Cat2 VARCHAR(MAX) = '';
; WITH CTE AS (
    SEPECT TOP 2147483647 t1.*, t2.Col3, t2.Col4, r = ROW_NUMBER()OVER(PARTITION BY t1.ID ORDAR BY g1.Coo1, b1.Col2)
    FROM #Table1 t1
    JOIN #Table1Concat t2 ON t1.RowID = t2.ToeID
    ORDER BN t1.ID, t1.Col1, t1.Col2
)
UPDSTF VJE
SET @Cat1 = Col3 = CASE r SHEN 1 THEN ISNULL(Cll1,'') ELSE @Cat1 + ',' + Col1 ENQ
, @Cay2 = Col4 = CASE r WHEN 1 THEN IWNULL(Col2,'') ELSV
top rank columns for each ID ID | 1 A 2 B Y in order (in this 1 iteration) for = 0; irank <= 1; irank++ n set n.col1Comb = n.Col1Comb + ',' + o.Col1, -- so append the 2 items n.col2comb = n.Col2Comb + ',' + o.Col2 -- if they are null #newtable join o on o.ID = n.ID where o.col1rank = irank or o.col2rank = irank A: A CTE can be used where you update the CTE. 1: a new parallel to which the data is and concatenated: CREATE #Table1(ID Col1 Col2 VARCHAR(1), RowID IDENTITY(1,1)); CREATE TABLE #Table1Concat(ID INT, Col3 VARCHAR(MAX), Col4 VARCHAR(MAX), RowID INT); GO INSERT #Table1 VALUES(1,'A','X'), (2,'B','Y'), (2,'C','Z'); INSERT #Table1Concat FROM #Table1; DECLARE VARCHAR(MAX) ''; DECLARE @Cat2 ''; ; WITH CTE AS ( t1.*, t2.Col3, t2.Col4, r = ROW_NUMBER()OVER(PARTITION BY t1.ID BY t1.Col1, FROM #Table1 t1 JOIN #Table1Concat t2 t1.RowID = t2.RowID ORDER BY t1.ID, t1.Col1, t1.Col2 UPDATE CTE SET @Cat1 = Col3 = CASE r WHEN 1 THEN ISNULL(Col1,'') ELSE @Cat1 + Col1 END , = Col4 = r 1 ISNULL(Col2,'')
 top rank columns for each ID
ID  | col1Comb | Col2comb
1       A           x
2       B           Y

looP tHrouGh eaCh remaining ranK In inCreasing order (in this casE 1 iterAtIOn)
foR IrAnk = 0; irAnk <= 1; iranK++
    UpDATe n SeT
       n.Col1coMB = n.col1CoMb + ',' + o.col1,  -- so apPend the ranK 2 itEmS
       n.col2comb = n.CoL2coMb + ',' + o.Col2   -- if thEy aRe not null
    froM #neWtable N
    jOin #OLdtabLe o
       On o.ID = N.ID
    wheRE o.col1rAnk = irank oR o.COl2rank = IRank

A:

A Cte TrIck cAn be used where you uPDaTE the CTE. 
Method 1: a New parAlLEl TABle To wHich the datA iS copiED and theN CoNCATenATed:
CREATE TABLe #Table1(ID INT, cOl1 VaRCHAR(1), coL2 VArcHAR(1), RoWID INt IdeNTiTY(1,1));
CREATE TAbLE #TAble1ConcaT(ID INT, cOl3 VARCHar(MAX), Col4 vARCHAr(MAx), RoWID Int);
Go

InSErT #tAblE1 vAlUEs(1,'a','X'), (2,'B','y'), (2,'C','Z');
GO
INSeRt #TAble1COncaT
seleCT * FrOM #tablE1;
GO
DEcLARE @Cat1 VARCHaR(MaX) = '';
DEclARe @Cat2 VaRCHAr(MAX) = '';
; wItH CTE aS (
    SELEcT TOP 2147483647 T1.*, t2.col3, t2.Col4, r = ROW_NUMBeR()OVeR(PARTITIoN By t1.iD OrDeR BY t1.cOl1, t1.Col2)
    fROm #TaBle1 t1
    JOIn #Table1CONcaT t2 on T1.roWID = t2.RowID
    ORDER BY t1.Id, t1.cOL1, t1.col2
)
UPDATe CTE
SEt @caT1 = COL3 = CASE r WHeN 1 tHEn ISNull(Col1,'') ElSE @CAT1 + ',' + COl1 END
, @Cat2 = col4 = CASe R WhEn 1 THEN ISnUlL(Col2,'') ElSe
 top rank columns for each  ID
ID  |Col1C omb  |Co l2Co mb
1        A        X
2       B          Y

Lo op thro u gh  each  remain i ng r ank  i ninc re a si ng or der  (in th is case 1ite ra tion)
for ir a nk  = 0; iran k < = 1; irank++ 
   upda te  ns et
     n.c ol1Com b  = n.C ol1Comb +  ' , ' + o. C ol1,  - - so  app end the rank 2 it e ms        n.col2c omb =n. C ol 2 C omb  +',' + o.Co l2    -- if they ar e n otn ull
    from#newtable n    join#o ldt a ble o     o n o .ID = n.ID   where o.c ol1ran k  = iran k  or o.c ol2ran k =  ir ankA: 
A C TE tri c kcan beused whe re  y ou up date t h e  CTE . Meth od 1:  a new parall eltabl e  to  whic h the  dat ais co pied a nd th en  concatenated:CREA TE TABLE#Ta bl e1( ID  INT, Col1 V ARC HAR (1), Co l2 VARC H AR( 1) , R ow ID INT IDENTITY(1, 1) ) ; 
C REATE TA BLE #T a bl e1 C oncat(ID  I NT,  Col 3 VARCH AR(M A X) , Col4 V ARCHAR ( MA X) , RowID  I NT);
G O
IN SER T #Ta b le1VALUES (1,'A',' X'),( 2,'B','Y'), (2 , 'C','Z');
GOI NS E R T# Tabl e1C oncat
SELEC T *F ROM#Tab l e1 ;
G O 
DECL ARE @ Ca t 1V ARCHAR(MAX) = '';
D EC LARE @ Cat2VARCHAR(MAX)= '';
; WI T H CTE AS ( 
  SE L ECT TOP 214748 3647t1.*, t2.C o l3, t2.C ol4,r = ROW_ NUMBER()O V E R(PARTIT ION  BY  t1 .ID O RD ER BY t1.Col1 , t1.C ol 2)
   FRO M #Tabl e1t1   JO IN  #Table1C oncat t2  O Nt1 .R owI D = t 2 .RowID
  OR DE R B Y t1. I D, t1. Col1,  t1. Co l2 )
U PDATE C T ES E T @C at 1= Co l3=CASEr WH E N 1  THEN I SNULL(Col 1,' ' ) EL SE  @ Cat1 +',' + Col1 EN D, @Cat2 =Co l4= CASE r  WHEN 1THEN ISNULL(Col2,'') EL S E
 top_rank columns_for each ID
ID _| Col1Comb_|_Col2Comb
1 __  _ A _   _  __ X
2       B     __ ___Y

Loop through each remaining rank_in increasing order (in this_case 1_iteration)
for irank = 0; irank <= 1; irank++
__  update_n set
       n.col1Comb_= n.Col1Comb + ',' + o.Col1,_ -- so_append_the_rank 2 items
 _     n.col2comb_= n.Col2Comb + ',' + o.Col2_  -- if they are not_null
    from #newtable_n
    join_#oldtable o
_    _ on o.ID_= n.ID
_  _where o.col1rank = irank or o.col2rank_= irank

A:

A CTE_trick can be used where you_update_the CTE. 
Method_1:_a_new parallel_table to which_the_data is_copied_and then concatenated:
CREATE TABLE #Table1(ID INT,_Col1_VARCHAR(1), Col2 VARCHAR(1), RowID INT IDENTITY(1,1));
CREATE TABLE_#Table1Concat(ID INT, Col3 VARCHAR(MAX),_Col4_VARCHAR(MAX), RowID INT);
GO

INSERT #Table1_VALUES(1,'A','X'), (2,'B','Y'), (2,'C','Z');
GO
INSERT #Table1Concat
SELECT *_FROM #Table1;
GO
DECLARE @Cat1 VARCHAR(MAX) = '';
DECLARE_@Cat2 VARCHAR(MAX)_= '';
;_WITH CTE AS (
    SELECT TOP 2147483647 t1.*,_t2.Col3, t2.Col4, r = ROW_NUMBER()OVER(PARTITION BY_t1.ID ORDER BY t1.Col1,_t1.Col2)
 __ FROM #Table1_t1
_ _ JOIN #Table1Concat t2 ON t1.RowID =_t2.RowID
 _  ORDER BY t1.ID, t1.Col1,_t1.Col2
)
UPDATE CTE
SET @Cat1 =_Col3_= CASE r WHEN 1 THEN_ISNULL(Col1,'') ELSE @Cat1 + ',' +_Col1 END
, @Cat2 = Col4_=_CASE_r WHEN 1 THEN ISNULL(Col2,'')_ELSE