SQL Server Performance

concate comments from different rows

Discussion in 'SQL Server 2005 General Developer Questions' started by terrence_chan99, Jan 8, 2007.

  1. terrence_chan99 New Member

    Here is my example.

    data
    ID CommentID Comments
    1 1 'app'
    1 2 'le'
    2 1 'or'
    2 2 'an'
    2 3 'ge'
    3 1 'banana'

    results want to get
    1 apple
    2 orange
    3 banana

    I was thinking to using the PIVOT, but as the number of row is unknown, I think it can't be use.

    any help is appreciate!
  2. Adriaan New Member

    Either loop through a cursor, storing the result strings in a temp table, or use a UDF that concatenates the Comments section, ordered by the CommentID, called for the distinct ID values:

    ... with apologies for the SQL 2000-like syntax ...

    This is the UDF:

    CREATE FUNCTION dbo.CompileComments(@ID INT)
    RETURNS VARCHAR(8000)
    AS
    DECLARE @Return VARCHAR(8000)

    SET @Return = ''
    SELECT @Return = @Return + t.Comments
    FROM dbo.MyTable t WHERE t.ID = @ID
    ORDER BY t.CommentID

    RETURN (@Return)

    GO

    And this is how you would call it:

    SELECT x.ID, dbo.CompileComments(x.ID)
    FROM (SELECT t.ID FROM dbo.MyTable GROUP BY t.ID) x
    ORDER BY x.ID

Share This Page