Concatinating Select Results

Hey everyone,

I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.

The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.

Here is what I am wanting to accomplish:

UPDATE tblWorkOrders
SET Comments = (SELECT Comments
FROM tblComments
WHERE tblWorkOrders.ReqNum =
tblComments.ReqNum)

I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.

I would appreciate any suggestions.

Thanks,

Lee.

There are probably a number of ways of doing this. The first thing that occurs to me is to use an asynchronous script component that takes a set of data (ordered by ReqNum). Inside the script component loop over the set of data, concatenating comments for each ReqNum.

-Jamie

|||Hey Jamie,

Thanks for the response. I'm very new to SSIS so that's a little over my head. Could you elaborate a little more on all that? Or can you think of an easier way of accomplishing this?

Thanks

Lee.