I want to Make query which will set Product Name to Product NAme + Category Name where Categories can be multiple
Update P Set P.Name = ISNULL(P.Name,'')+','+ C.NAme
from Product P
Left Outer Join Category C On P.CategoryId = C.Id.
Now one Product can have multiple categories E.g. Alpenliebe Jar product can have Multiple Categories as Toffies and Candies I want to set its Name to Alpenliebe Jar,Toffies,Candies My current query works but only setting name to Alpenliebe Jar,Toffies
Any Help
Try this...
UPDATE P SET P.Name = P.Name+'-'+ SUBSTRING((SELECT ', ' + C.NAme FROM Product P INNER JOIN Category C ON P.CategoryId = C.Id
WHERE P.Name = PH.Name
ORDER BY P.Name FOR XML PATH('')),2,200000) FROM Product PH
Try this:-
Update p
Set P.Name = STUFF((
SELECT P.Name + ',' + IsNull(CName,'')
FROM Category c
WHERE p.CategoryID = c.ID
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
from Product p
Try like this
DECLARE @Names VARCHAR(8000)
SET @Names = SELECT COALESCE(@Names + ',', '') + C.Name FROM Category JOIN Product P On P.CategoryId = C.Id.
Update P Set P.Name = ISNULL(P.Name,'')+ ',' + @Names
FROM Product P
WITH cte(Id,Name)
AS
(
SELECT ID, Name
FROM Category
WHERE [CategoryId] IS NULL
UNION ALL -- starts recursion
SELECT ID, P.Name +',' C.Name
FROM Category C
JOIN cte P ON P.ID = C.[ParentId]
)
Update P Set P.Name = C.NAme + ISNULL(P.Name,'')
from Product P
Left Outer Join Category C On P.CategoryId = C.Id
FOR XML PATH