concate comments from different rows

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!

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