Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    Timisoara
    Posts
    3

    Unanswered: Concatenate string

    Please help me with this if you can.
    I have one table with CustomerID and some other data.
    In other table i have CustomerID(the link with the first table) and Agent
    The relation of the first with the second one is ONE TO MANY.
    I want something like this:
    Customer,'Agent1,Agent2,Agent3'

    Is it possible.
    Please help

  2. #2
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    maybe...

    looks like you are trying to reverse pivot...

    1) Are there always three agent rows per customer id ?
    2) Is there a field which identifies the agent number per customer id?

    throw up the ddl to these tables

  3. #3
    Join Date
    Oct 2004
    Location
    Timisoara
    Posts
    3
    First Table
    CustomerID|CustomerName

    SecondTable
    CustomerID|AgentName

    Ex Records:

    First Table
    1| Gigi
    2|Vasile

    Second Table
    1|Ionescu
    1|Vasilescu
    1|George
    2|Marin
    2|Preda

    I want:
    1|'Gigi'|'Ionescu,Vasilescu,George'
    2|'Vasile'|'Marin,Preda'

  4. #4
    Join Date
    Oct 2004
    Location
    Timisoara
    Posts
    3
    I did find a solution.
    But i'm not sure that is the best one.
    I use a function in MS SQL Server and a Cusor inside.
    Here it is the function.
    Anyway, i know that Cursors are slowly so please give me another solution if you know.


    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO



    ALTER FUNCTION admin_GetAgentsNames (@CustomerId int)
    RETURNS varchar(1000)
    AS

    BEGIN
    DECLARE @AgentsNames VARCHAR(1000)
    DECLARE @AgentFName VARCHAR(50)
    DECLARE @AgentLName VARCHAR(50)
    DECLARE @AgentId INT
    SET @AgentsNames = ''
    DECLARE curGetAgents CURSOR FOR
    SELECT DISTINCT AgentId,ISNULL(c.FNAME,''),ISNULL(c.LNAME,'')
    FROM tbl_customerToLocalAgent_Pass AS a
    JOIN tbl_Customer AS b ON a.AgentId=b.CustomerId
    JOIN tbl_CustomerDetails AS c ON b.CustomerId=c.CustomerId
    AND b.PreferredLanguageId=c.LanguageId
    WHERE a.CustomerId=@CustomerId

    --Opent the cursor
    OPEN curGetAgents
    --Fetch the first record
    FETCH NEXT FROM curGetAgents INTO @AgentId,@AgentFName,@AgentLName

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    --Concatenate the names
    SET @AgentsNames = @AgentsNames + ',' + @AgentFName + ' ' + @AgentLName
    --Get the next row
    FETCH NEXT FROM curGetAgents INTO @AgentId,@AgentFName,@AgentLName
    END
    --Close cursor
    CLOSE curGetAgents
    DEALLOCATE curGetAgents

    RETURN(RIGHT(@AgentsNames,LEN(@AgentsNames)))

    END

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •