You must Sign In to post a response.
  • Category: SQL Server

    Need to get the multiple row in single

    Hi Friends,

    Here is me table:

    id Name
    ----- ---------
    1 Raj
    1 Kumar
    2 A
    2 B

    i need result as 1 | Raj,kumar
    2 | A,B

    I have seen something called STUFF, but dont know how to use it. Any idea???

    Thanks in advance.
  • #767845
    Hi,

    Yes using STUFF we can achieve this, refer below sample query for your reference


    WITH tempTable AS
    (
    SELECT ID FROM Tablename
    GROUP BY ID
    )

    SELECT t.ID, Name= STUFF((SELECT ',' + Name FROM Tablename tn WHERE tn.ID = t.ID FOR XML PATH('')), 1, 1, '')
    FROM tempTable t;


    Hope this helps you....

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #767846
    Hi Naveen,

    I tried that query and getting error like Syntax Error near XML. Am using SQL SERVER 2003

  • #767857
    I feel SQL SERVER 2003 version support STUFF.
    Try the below code.


    SELECT Id,
    Stuff((SELECT ', ' + code
    FROM tblmta t2
    WHERE t2.Id = t1.Id
    FOR XML PATH('')), 1, 2, '') [Name]
    FROM tblmta t1 GROUP BY ID

    Thanks,
    Mani


Sign In to post your comments