Need matching comma separated value from 1 table column to update in another table column using like

Transact-SQL
, ,
1

suppose in table tbl_Nconfigurable i have column name sku
which have one value "NT-10-COFFEE"

and in table tbl_Nsimple column sku i have values
NT-10-COFFEE-L
NT-10-COFFEE-M
NT-10-COFFEE-S
NT-10-COFFEE-XL

i need output as
(col1) tbl_Nconfigurable.sku (col2) tbl_Nconfigurable.concatvaluse
(col1 data) : NT-10-COFFEE (col2 data): NT-10-COFFEE-L,NT-10-COFFEE-M,NT-10-COFFEE-S,NT-10-COFFEE-XL

Below i have tried this querry
Select sku,
STUFF((SELECT ',' + sku FROM tbl_Nsimple a WHERE (
a.sku like '%'+ Result.sku+'%') FOR XML PATH ('')),1,1,'') AS concatvaluse
From tbl_cinfigurable AS Result
GROUP BY sku

Kindly help plz

2

I think all you need to do is remove that. If you have duplicate values for [sku] in [tbl_cinfigurable] then you could do

SELECT	sku,
	STUFF(
	(
		SELECT	‘,’ + sku
		FROM	tbl_Nsimple a
		WHERE a.sku LIKE ‘%’+ Result.sku+’%’
		FOR XML PATH(''), TYPE
	).value('.', 'varchar(max)')
	,1,1,’’) AS concatvaluse
FROM
(
	SELECT	DISTINCT sku
	FROM	tbl_cinfigurable 
) AS Result
GROUP BY sku

note that I have modified your XML PATH expression to a version which is more robust

1 Like
3

Thank you so much ill get back to u after testing it.

4

Hi,
i tried the code its not working
sku concatvaluse
NULL NULL
6701-N.BLUE NULL
6701-RED NULL
6702-BLUE NULL
6703-GREEN NULL
6703-PEACH NULL
6704-BLACK NULL
6704-BUTTER NULL
6705(I)-BLUE NULL
6705(I)-PINK NULL
6706-BEIGE NULL
6706-N.BLUE NULL

5

Blockquote
declare @temp table (csku nvarchar(max),ssku nvarchar(max))
insert into @temp(csku,ssku)
Select cn.sku'CSku'
,si.sku

from tbl_Nconfigurable cn

left join tbl_Nsimple si on si.sku like CONCAT('%', cn.sku, '%')

SELECT DISTINCT
t.csku,
STUFF((SELECT ', ' + i.ssku /* notice this line is different */
FROM @temp i
WHERE i.csku = t.csku
FOR XML PATH ('')), 1, 2, '') as New
FROM
@temp t

I solved it with this