CODEWITHSUNDEEP
sqlms-access
Robus
Robus

Reputation: 8259

SQL Concatenation Many to Many

I have three tables like this:

Items:

enter image description here

Categories

enter image description here

and a simple MtM table to connect them

enter image description here

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

Upvotes: 5

Views: 173

Answers (2)

user3646932
user3646932

Reputation: 496

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

DAta

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:

resuklts

Upvotes: 1

Kevin Francis
Kevin Francis

Reputation: 385

You could loop through a record set with code to output the results your looking for, but that would require either VBA or C#. Access doesn't support CTE like SQL Server, so that removes another very nice option. It may be possible using a pivot table, and concatenating the resulting fields. Would be tricky but I believe it could be done.

Create a crosstab query as such...

TRANSFORM First(Categories.CategoryNar) AS FirstOfCategoryNar
SELECT MtM.ItemID
FROM Categories INNER JOIN MtM ON Categories.ID = MtM.CategoryID
GROUP BY MtM.ItemID
PIVOT MtM.CategoryID;

Then create a query based on the crosstab query like this...

SELECT Crosstab.ItemID, [1] & [2] & [3] AS ConcatField
FROM Crosstab;

The numbers are CategoryIDs.

enter image description here

I hope this helps.

Upvotes: 0

Related Questions