CODEWITHSUNDEEP
sql.netms-accessconcatenationms-access-2013
LLLL_easycomeeasygo
LLLL_easycomeeasygo

Reputation: 63

MS Access 2013 Concatenate Rows query

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.

Upvotes: 1

Views: 393

Answers (1)

Parfait
Parfait

Reputation: 107767

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.

Upvotes: 2

Related Questions