Output one-to-many as single line csv 1

[addy]

Apologies if this is not the right board - wasn't sure if this should go here or in the Queries section!

OK, I have two table, one has a list of Users and the other has a list of Divisions they could belong to. So, something like:

Table 1:

UserID
Username
etc.

Table 2:
UserID
DivID

A User can exist in more than one Division. I need to be able to produce a list showing the DivIDs for each User as a csv string. So, if my data looked like:

Table 1:

User ID
1
2
3
4

Table 2:

User ID DivID
1 1
1 2
2 1
2 2
2 3

I need to get:

User ID DivID's
1 1, 2
2 1, 2, 3

Any ideas?

I've thought about creating a Recordset and looping through each record creating the csv and checking the UserID of the current record to the next record to see if it is the same (continue building the csv) or different (start a new csv) but I am wondering if there is a better way.

Thanks.

 

[alvechurchdata]

If you create a forward-only recordset in UserID/DivID order then you'll stream the records through your loop and generate the csv in a single pass through the records. I'm sure there's a fancy SQL way of doing it but I can't see that it will be much faster. I'd go with the simple loop.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[addy]

PHV - Exactly what I was looking for!!

I searched the FAQs but obviously didn't use the right terms.

Many, many thanks.