How to replace CSV values with new CSV vals in SQL 2012

Question

Hi, please ref my below codes, I want to update #T1 CSV numbers based on T2 table, share the script for this.

CREATE TABLE #T1 (  
	Rowid INT Primary key,  
	CSV_OLD Nvarchar(200),  
	CSV_New  Nvarchar(200)
	)  
  
Insert into #T1 (Rowid,CSV_OLD,CSV_NEW) Values ('1','5,386,7,8','')  
Insert into #T1 (Rowid,CSV_OLD,CSV_NEW) Values ('2','110,34,119','')  


CREATE TABLE #T2 (  
	Code_Old INT,  
	Code_New  INT
	)  
  
Insert into #T2 (Code_Old, CODE_NEW) Values (5,1005 )  
Insert into #T2 (Code_Old,CODE_NEW) Values (119,1019)  
Insert into #T2 (Code_Old,CODE_NEW) Values (6,1386)  
Insert into #T2 (Code_Old,CODE_NEW) Values (7,1007)  
Insert into #T2 (Code_Old,CODE_NEW) Values (8,1018)  
Insert into #T2 (Code_Old,CODE_NEW) Values (110,1110) 
Insert into #T2 (Code_Old,CODE_NEW) Values (34,1034)  

I want to update codes into #T1 CSV_NEW Column, based on #T2 and expected result as follows

Rowid	CSV_OLD	    CSV_New
1	    5,386,7,8	1005,1386,1007,1008

Answer 1

Hi @Aypn CNN

There is something weird in your desired result, check below screenshot. I thought it might be a typo.

If I was right, then please check this query:

--Using STRING_AGG which is supported in SQL Server 2017 (14.x) and later
SELECT Rowid,STRING_AGG(#T2.Code_New,',') WITHIN GROUP (ORDER BY A.[key]) AS Code_New
INTO #T3
FROM #T1 OUTER APPLY OPENJSON( '[' + CSV_OLD + ']', '$') A
	     LEFT JOIN #T2 ON #T2.Code_Old = A.value
GROUP BY Rowid
 
UPDATE #T1
SET CSV_NEW=(SELECT Code_New FROM #T3 WHERE #T3.Rowid=#T1.Rowid)

--Use STUFF if STRING_AGG was not supported
;WITH CTE1 AS
(
 SELECT Rowid,#T2.Code_New,A.[key] AS [Order]
 FROM #T1 OUTER APPLY OPENJSON( '[' + CSV_OLD + ']', '$') A
	      LEFT JOIN #T2 ON #T2.Code_Old = A.value
)
SELECT Rowid
	  ,STUFF((SELECT ',' + CAST(Code_New AS VARCHAR) FROM CTE1 WHERE Rowid=T.Rowid ORDER BY [Order] FOR XML PATH('')),1,1,'')Code_New
INTO #T3
FROM (SELECT DISTINCT Rowid FROM CTE1)T

UPDATE #T1
SET CSV_NEW=(SELECT Code_New FROM #T3 WHERE #T3.Rowid=#T1.Rowid)

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.