MS Access 2013 Concatenate Rows query

Question

I'm looking for something like FOR XML PATH ('') for MS Access.

Since I can only read data but not modify the access file/database, everything beyond querying is not an option for me.

A little detail:

My target is to have some query like below but usable for MS Access 2013:

SELECT 
    Items.Name, Items.Description , 
    (SELECT ItemValues.Value + ';'
     FROM Itemvalues 
     WHERE Itemvalues.ItemID = [value] 
       AND someOtherConditions
     FOR XML PATH ('') ) AS Values 
FROM 
    Items 
WHERE 
    Items.ID = [value]

If the results of ItemValue selection will be like

SELECT ItemValues.Value
FROM Itemvalues 
WHERE Itemvalues.ItemID = [value] 
  AND someOtherConditions

Output:

     itemval1
     property2val
     1234foo

And the result of Item Selection will be like

SELECT 
    Items.Name, Items.Description 
FROM 
    Items 
WHERE 
    Items.ID = [value]

Output:

 Testitem | This is a test item

I want to have a result row like

 Testitem | This is a text test item | itemval1;property2val;1234foo;

Thanks your for help

PS: I've seen some other posts about this topic but since those are either many years old or not fit for my situation I'm trying my luck.

I see that you have added the `.net` tag to this question. What is the environment in which you intend to run this query? C#? VB.NET? ...? — Gord Thompson, Oct 19 '15 at 17:44

score 2 · Accepted Answer · edited May 23 '17 at 11:53

Consider the following query with subquery and derived table nested in an aggregate query. Inner query counts number of ItemValues per ItemID and then outer query conditionally aggregates by [Name] and Description using calculated count to output a concatenation string of Item's Values.

Unfortunately, you will need to explicitly add Max(IIF(...) query statements for each value to be concatenated as below only uses three from your example. See ellipsis.

Please note: this solution assumes you have a primary, autonumber ID field in [ItemValues] table:

SELECT Items.Name, Items.Description,
       Max(IIF(ItemCount=1, t1.Value, NULL)) & ';' &  
       Max(IIF(ItemCount=2, t1.Value, NULL)) & ';' & 
       Max(IIF(ItemCount=3, t1.Value, NULL)) 
       ... As ItemValues       
FROM(
     SELECT Items.Name, Items.Description, t1.Value,
            (SELECT Count(*) FROM ItemValues t2 
             WHERE t1.ItemID = t2.ItemID AND t1.ID >=t2.ID) As ItemCount
     FROM Items INNER JOIN ItemValues t1 ON Items.ID = t1.ItemID
) As derivedTable
GROUP BY Items.Name, Items.Description;

OUTPUT

Name        Description            ItemValues
Testitem    This is a test item    itemval1;property2val;1234foo

By the way, your Items and ItemValues table seems to resemble the Entity-Attribute Value (EAV) model where multiple data items and types are stored in a single column. Though it makes for efficient storage at an extreme type of normalization, this model is generally not recommended in database design. See SO posts on the subject: here and here. Consider a revised table schema to avoid complex queries down the road.

thanks for you extensively answer but since I don't know how much values i will get I don't think I can do it this way and I also don't think I'll be allowed to change their database structure when I'm even not allowed to add a module — LLLL_easycomeeasygo, Oct 20 '15 at 07:40
You can count the values beforehand and then add the `Max(IIF(...` lines accordingly. Even VBA can help as it can create the querydef, using a loop across items. — Parfait, Oct 20 '15 at 13:30
Yes you right I'll query ItemValues first and then build my actual query to get the data. Instead of using loops in loops ............ — LLLL_easycomeeasygo, Oct 20 '15 at 13:56

MS Access 2013 Concatenate Rows query

1 Answers1