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

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