CURSOR/FETCH Issue

Hey Everyone,

I have an issue with pulling data out of one table and concatinating it into another table. There is a table with a list of Work Orders (tblWorkOrders) that has a one-to-many relationship with a table that contains a list of Work Order Comments (tblComments).

I want to concatenate all the work order comments together into one Text field in the work orders table for each work order. For example, if tblWorkOrders.ReqNum matches three records with tblComments.ReqNum, I want to concatenate all three tblComments.Comment together and put them in tblWorkOrders.Comments for the one record in tblWorkOrders that matches that same ReqNum.

I've been playing around with the CURSOR/FETCH combo, but I am having no luck. Please help!!!!!

Here is my code:

USE SolidWaste;
GO
DECLARE C1 CURSOR
FOR
SELECT c.Comment
FROM tblComments AS c
INNER JOIN tblWorkOrders AS w
ON c.ReqNum = w.ReqNum;
OPEN C1;
DECLARE @comments varchar(500);
FETCH NEXT FROM C1 INTO @comments;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
UPDATE tblWorkOrders
SET tblWorkOrders.Comments = @comments
END;
FETCH NEXT FROM C1 INTO @comments;
END;
CLOSE C1;
DEALLOCATE C1;

The first problem with this code is that it pulls every single comment from tblComments and not just the comments for the corresponding work order (tblWorkOrders.ReqNum = tblComments.ReqNum). The second problem is that it does not concatenate at all; each FETCH replaces the contents of tblWorkOrder.Comments. The the results are that the contents of the Comments field for every single record in tblWorkOrders is the very last comment resulting from the join.

This is my first experience with using cursors so please don't laugh too hard at my code. I'm sure that the problem is in my loop, which is also a new thing for me. I assumed that populating the CURSOR with an INNER JOIN would just pull one matching set at a time, but I obviously don't know what I am doing.

Thanks in advance.

Lee.