Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/articles/sql-server/)
-   -   Concatenating data with Multiple Result(s) using FOR XML PATH (http://www.go4expert.com/articles/concatenating-data-multiple-result-s-t24912/)

mail.yuva 9Feb2011 12:34

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



All times are GMT +5.5. The time now is 19:15.