concate comments from different rows | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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
]]>