Concatenating data with Multiple Result(s) using FOR XML PATH

mail.yuva's Avatar author of Concatenating data with Multiple Result(s) using FOR XML PATH
This is an article on Concatenating data with Multiple Result(s) using FOR XML PATH in SQL Server.
Concatenating data with Multiple Result(s) using FOR XML PATH

Code:
Create table #TempProducts(Items Varchar(30),Colour Varchar(15))
 
Insert into #TempProducts values('Table','Red');
Insert into #TempProducts values('Table','Green');
Insert into #TempProducts values('Table','Blue');
Insert into #TempProducts values('Chair','Black');
Insert into #TempProducts values('Chair','Pink');
Insert into #TempProducts values('Chair','Blue');
 
WITH Concatenation(Items,Colour) AS
( SELECT A.Items,
             (SELECT C.Colour + ',' FROM #TempProducts C WHERE 
  C.Items=A.Items FOR XML PATH('')) 'Colour'
  FROM #TempProducts A
  GROUP BY A.Items
)
 
SELECT Items,LEFT(Colour,LEN(Colour)-1) as Colour
FROM Concatenation
 
GO
Drop table #TempProducts
Mittu, suraj2011 likes this