locked
Retrieve the data from table RRS feed

  • Question

  • Hi sir,

    I have a table,it contained EID,Ename,Deptno and i want to retrieve data like deptno,all Enames with comma seperation(30,ramu,subbu,suman,raja) then deptno,all Enames with comma seperation like this how can i get, it should be taking all enames all under perticular deptno

    Thank

    • Moved by Ed Price - MSFTMicrosoft employee Sunday, January 22, 2012 6:09 AM Wrong forum (From:MSDN, TechNet, and Expression Profile and Recognition System Discussions)
    Wednesday, January 4, 2012 8:38 PM

Answers

All replies

  • In SQL Server 2005 and up try

    SELECT DeptNo, STUFF((SELECT ', ' + EName from dbo.Employees E1 where E1.DeptNo = E.DeptNo ORDER BY EName FOR XML PATH('')),1,2,'') as ENames
    
    FROM dbo.Employees E 
    
    GROUP BY E.DeptNo
    
    ORDER BY E.DeptNo
    

     

    Take a look at these blog posts explaining this solution in details:

    MSDN thread about concatenating rows
    Making a list and checking it twice
    Concatenating Rows - Part 1
    Concatenating Rows - Part 2

    BTW, it will be better to move your post to T-SQL forum.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Hasham NiazEditor Thursday, January 5, 2012 4:45 AM
    • Marked as answer by Peja Tao Monday, February 6, 2012 8:52 AM
    Wednesday, January 4, 2012 8:43 PM
  • Hi sir,

    I have a table,it contained EID,Ename,Deptno and i want to retrieve data like deptno,all Enames with comma seperation(30,ramu,subbu,suman,raja) then deptno,all Enames with comma seperation like this how can i get, it should be taking all enames all under perticular deptno

    Thank

    @Moderator : Please move this thread to Transact-SQL Forum as question is related to T-SQL.

    Thanks, Hasham

    Thursday, January 5, 2012 4:46 AM
    Answerer