SQL Concatenation Many to Many

Question

I have three tables like this:

Items:

Categories

and a simple MtM table to connect them

For reporting purposes I've been wondering if it would be possible to create a field with the concatenation of all categories the item belongs to. For example, if item ID=1 would belong to categories ID=1 and ID=2; I could do a select on Items and get a field 'Categories' with the value 'Schuhe; Hemde'

Is this possible with SQL alone at all?

The best I could come up with

SELECT Items.*, Categories.CategoryName
FROM (CategoryItemAffinities 
      INNER JOIN Categories ON CategoryItemAffinities.CategoryID = Categories.ID) 
INNER JOIN Items ON CategoryItemAffinities.ItemID = Items.ID;

But this obviously yields more than one result per item

[edit] Just to specify, ms access is merely the db engine, I'm not using access forms/reports etc per se. I need this for a C# app

Answer 1

Jet DB does not contain an aggregate function for string concatenation. You can create your own function to do what you need. First, you want your data with the itemID and the STRING value of the category, as such:

yourTableOrQueryName

then write a custom function like so:

Public Function getCats(theID)
    Dim rs As Recordset
    Dim db As Database
    Dim qstr As String
    Dim returnString As String

    qstr = "SELECT category FROM yourTableOrQueryName WHERE itemID = " & theID

    Set db = CurrentDb
    Set rs = db.OpenRecordset(qstr)

    Do While Not rs.EOF
        returnString = returnString & ", " & rs!Category
        rs.MoveNext
    Loop

    getCats = Mid(returnString, 3, Len(returnString) - 2) 

End Function

Then your query looks like this:

SELECT itemID, getCats([itemID]) AS catList
FROM yourTableOrQueryName
GROUP BY itemID;

That query can be optimized so it doesn't run multiple times for each ID by nesting a select unique query that just gets your unique IDs before you run the "getCats()" function, if you are into trimming milli secs, but I leave that to you.

Results: