1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'SQL Server' started by mail.yuva, Feb 9, 2011.

  1. mail.yuva

    mail.yuva New Member

    Joined:
    Jan 10, 2011
    Messages:
    44
    Likes Received:
    9
    Trophy Points:
    0
    Occupation:
    Software Developer
    Location:
    Coimbatore
    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 
    
     
    suraj2011 and Mittu like this.

Share This Page