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
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
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