Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    5

    Unanswered: Concatenating one-to-many relationship into a single column

    Hi,

    I need to concatenate a field from certain number of rows. I created a function to return the concatenated value which will be a part of another view/procedure to be used for reporting purposes.

    Here's sample data:
    iIncidentID iWorkNoteID iseqNum workNoteAll
    1 1 1 notes1(1275 chars)
    2 1 1 notes2(1275 chars)
    2 1 2 notes3(1275 chars)
    3 1 1 notes4(1275 chars)
    3 1 2 notes5(1275 chars)
    3 1 3 notes6(1275 chars)

    Final output
    iIncidentID workNoteALL
    1 notes1(1275 chars)
    2 notes2 notes3
    3 notes4 notes5 notes6

    final woorkNoteAll will be a part of a query in another view which contains many other fields.

    Here's the function. I'm passing an ID and based on that ID, the function returns a string. However, when I tested the function it's giving me a null.

    /*
    --Calling syntax:: Select dbo.getIncidentNotes(187714) as 'Notes'
    --Function to get all the latest notes for an incident
    */
    CREATE FUNCTION dbo.getIncidentNotes(@iIncidentID int)
    RETURNS varchar(8000)
    AS
    BEGIN
    DECLARE @allnotes varchar(8000)
    DECLARE @seqnotes varchar(255)
    DECLARE @seqnum int
    DECLARE @counter int
    SELECT @counter=1

    SELECT @seqnum = max(iseqnum) from dbo.frs_weekly_prospect_status2 where iIncidentId=@iIncidentID

    WHILE (@COUNTER <=@seqnum)
    BEGIN
    SELECT @seqnotes = workNoteALL from dbo.frs_weekly_prospect_status2 where iIncidentId=@iIncidentID and iSeqNum=@counter
    SELECT @allnotes = @allnotes + @seqnotes
    SELECT @COUNTER = @COUNTER + 1
    END --While Begin
    RETURN @allnotes
    END

    Can someone please tell me what's wrong with the code?

    I really appreciate it.
    Thanks in advance.

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    intitalize the return value

    set @allnotes = ''

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try this method instead. Should be much faster than your loop.
    Code:
    /* 
    --Calling syntax:: Select dbo.getIncidentNotes(187714) as 'Notes'
    --Function to get all the latest notes for an incident
    */
    CREATE FUNCTION dbo.getIncidentNotes(@iIncidentID int)
    RETURNS varchar(8000)
    AS
    BEGIN
    declare	@allnotes varchar(8000)
    
    select	@allnotes = coalesce(@allnotes, '') + workNoteAll
    from	dbo.frs_weekly_prospect_status2
    where	iIncidentId=@iIncidentID
    
    return	@allnotes
    END
    (Hint: You might want to insert a comma or a spacer between your notes...)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    I knew there was a different solution using coalesce

    Thank you blindman haven't used coalesce much in the past will use in the future and get rid of those ^&*(*%&%&(&## cursors

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Get those ^&*(*%&%&(&## cursors out of your ^&*(*%&%&(&## database!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2006
    Posts
    5
    many thanks for the replies / solutions.

Posting Permissions

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