How to Concatenate Data from the 'Many'
Side of a Relationship

I found this question in the archives but couldn't find the answer.

If anyone has the answer I would be eternally grateful


I have the following data in a 2 column table:

Column1 Column2
------------------ ------------------------
Beverages Products Chai
Beverages Products Chang
Beverages Products Chartreuseverte
Beverages Products Cote de Blaye
Beverages Products Guarana Fantastica
Condiments Products Aniseed Syrup
Condiments Products Chef
Condiments Products Anton's Cajun Seasoning
Condiments Products Chef Anton's Gumbo Mix
Condiments Products Genen Shouyu, Grandma's

Is there an easy way to concatenate values from column2 to show:

Column1 Column2
------------------ ------------------------
Beverages Products Chai, Chang, Chartreuseverte, Cote de Blaye, etc.
Condiments Products Aniseed Syrup, Chef, Anton's Cajun Seasoning, etc.=

There really isn't a 'good' way to do this, but see
http://p2p.wrox.com/topic.asp?TOPIC_ID=1787

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Many thanks for such a prompt response Jeff.

I'm a complete novice at this game. Would I be correct in assuming that, whereas this fix could work for SQL Server 2000, the code you suggest wouldn't work for MS Access?

Mike

I haven't tried such a thing in Access, but I wouldn't be surprised if a similar approach couldn't be made to work. In fact, since Access allows er, access, to functions and variables defined "outside" of SQL statements, it might actually be easier to accomplish.

It's still wrong, though; the result of such a concatenation violates first normal form...

It is my opinion this is a presentation issue and not a query issue, so it should be handled at the client. The resultset is returned to client code as multiple rows, and the client manipulates them and combines them however it wants for display purposes.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks again for the assistance. I'll persevere and advise how I progress.

Mike