I think a simple (but fictitious) example is the best way to express what I
am trying to do.

I have 4 tables.

Family:
ID
Description

Person:
ID
FamilyID
Name

Person_Car:
ID
PersonID
CarID

Car:
ID
Make
Model
Year


This setup allows me to have many common types of cars in the Car table, and
keep up with many families and the types of cars that each of the families'
members drive.  (Assume each family has a few teenage drivers and the
parents and teenagers share the family's cars.  Since we'll have many
families in this db we'll surely have the same type of car duplicated.  Thus
the need for the Person, Person_Car, Car tables.)  So, an entry exists in
Person_Car only when a particular person can drive (i.e. has access to) this
car model (in terms of its make, model and year).  And obviously, a
particular car model can be driven by many people.  What I am literally
trying to do is a join of these tables, but only have one row per person and
for each row have a comma-delimted list of the cars that person drives.  For
example, a typical joinining of these tables might produce something like
this for each person:

Family.Description    Person.Name        Car.Make    Car.Model    Car.Year
....
The Smiths               John Smith            1999             Ford
Mustang
The Smiths               John Smith            2002             Honda
Accord
The Smiths               John Smith            1989             Ford
Bronco
The Browns             July Brown            1999             Ford
Mustang
The Browns             July Brown            2005             Toyota
Celica
....

What I am trying ultimately display to my users is, for each person:

Family.Description    Person.Name        Cars
....
The Smiths                John Smith            1999 Ford Mustang, 2002
Honda Accord, 1989 Ford Bronco
The Browns              July Brown            1999 Ford Mustang, 2005 Toyota
Celica
....

I know I could take the result of the first type of query and create the
desired output (i.e. this comma delimted string of cars per person) via
application code, but I'd like to have the database do this work for me.
Any ides on how to achieve the results I want via SQL?

Thanks!

See:
http://www.aspfaq.com/show.asp?id=2529

Razvan

How about this one?
http://milambda.blogspot.com/2005/07/return-related-values-as-array.html

And please read more posts in this newsgroup to find out about the dangers
concerning the use of such concatenation methods.


ML

Select Query
Multilanguage support
SQL performance problem
Counting Records
Combining Two Tables ?