How to Concatenate Multiple Rows into a Single String in MS Access?

Join rows in MS Access query

Suppose you have an MS Access table with two columns (ColumnA and ColumnB) containing pairs of data. Your goal is to combine the values ​​in ColumnB into a single concatenated string for each unique value in ColumnA.

Solution:

To do this, you can use a function to perform the join operation. The GetList function retrieves a list of values ​​from a query and concatenates them using specified delimiters.

Query:

<code class="language-sql">SELECT T.ColumnA, GetList("Select ColumnB From Table1 As T1 Where T1.ColumnA = " & [T].[ColumnA],"",", ") AS ColumnBItems
FROM Table1 AS T
GROUP BY T.ColumnA;</code>

Instructions:

• Outer query (SELECT) Groups rows by ColumnA.
• The inner query (GetList) gets the ColumnB value for each ColumnA value and concatenates them using a comma (",") as the delimiter.
• The generated table (ColumnAItems) contains the concatenated strings.

Output:

<code>ColumnA | ColumnBItems
------- | ---------
1       | abc, pqr, xyz
2       | efg, hij
3       | asd</code>

The above is the detailed content of How to Concatenate Multiple Rows into a Single String in MS Access?. For more information, please follow other related articles on the PHP Chinese website!