How to flatten out a one to many relationship

[vtisgreat]

I am having difficulty in SSIS to be able to take two tables in a one to many relationship, and flatten the resulting joined rows into one wow with a comma separated field representing the repeating data.

I am creating a flat file for 100s of records. Each record may point to several items in another tablble. This repeating item should be combined into a comma separated field with up to 3 (ignore more) items in it. Example

Table 1
1
2

Table 2
1 "A"
1 "B"
1 "C"
1 "D"
2 "E"

results to
1, "A,B,C"
2, "D"

given that table 1 may have 100s of entries, I want to make sure that it is not too slow.

Any thoughts???

 

[mrdenny]

You'll want to use a SQL function to do this. Unfortunately with this kind of query there is no way to make sure that it isn't to slow. There are several examples in forum183.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog