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