Post reply

Query Result Coma Delimited

  • August 11, 2005 at 1:35 am

    #66792

    Hi,

     Sample Data

     Company

    =============

    CoId                  CoName

    1                      Company1

    2                      Company2

    3                      Company3

      

    Employee

    ============

    EmpId               EmpName

    1                      Employee1

    2                      Employee2

    3                      Employee3

    4                      Employee4

    5                      Employee5

      

    Manager

    =============

    ManId               Company                      Employee

    1                      1                      1

    2                      1                      2                     

    3                      1                      3

    4                      2                      4

    5                      2                      5

      

    I want Following Result

     

    Company                      Manager

               

    Company1                     Employee1,Emplyee2,Employee3

    Company2                     Employee4,Employee5

     

    I do not want to write a function and use any cursore for that is there any way that i could generate this result in a single query using joins and some syetem defined function?

     Vijay Soni

    a2z Infotech Pvt. Ltd.

    308, ACME Plaza, M.V. Road

    Andheri (E), Mumbai-59

    Telefax022) 550 21759/60

    <mailto:vijay@a2zinc.net>

    <http://www.a2zinc.net/&gt;

    "Web-based software for managing and marketing events"

  • August 11, 2005 at 2:16 am

    #581308

    Here is one solution that does not use cursors, but since it calls a UDF it will suffer if there are many rows. Normally I would do this kind of thing on the client instead of in the query.

    CREATE FUNCTION dbo.Employees (@coId INT)

    RETURNS VARCHAR(8000)

    AS

    BEGIN

      DECLARE @emps VARCHAR(8000)

      SET @emps = ''

      SELECT @emps = @emps + e.EmpName + ','

      FROM Employee e

      INNER JOIN Manager m ON e.EmpId = m.Employee

      WHERE m.Company = @coId

      RETURN CASE WHEN @emps = '' THEN NULL ELSE LEFT(@emps, LEN(@emps) - 1) END

    END

    SELECT CoName, dbo.Employees(CoId)

    FROM Company

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • August 11, 2005 at 3:06 am

    #581314

    Hi,

    Solution Worked.

    Thnaks For The Support

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply