How Can I Concatenate Rows in MS Access Based on a Grouping Column?

Join rows in MS Access query

Introduction

This article explores the challenges encountered in Microsoft Access when trying to join the values ​​of the second column in a table based on the first column. Our goal is to achieve this transformation through queries.

Question

Consider a table containing the following data:

Our goal is to concatenate the values ​​in ColumnB into a single comma-separated string for each unique value in ColumnA. The desired result is as follows:

Solution

To achieve this connection, we use a custom function called "GetList". This function accepts a SQL query as a parameter and iterates over the recordset returned by the query, concatenating the values ​​into a single string.

The following is a query containing the "GetList" function:

<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>

Description

1. Outer query (SELECT T.ColumnA) retrieves unique values ​​in ColumnA.

2. The GetList function is used to concatenate the values ​​in ColumnB based on the current value of ColumnA. The parameters of the GetList function are:

   • Subquery: SELECT ColumnB FROM Table1 AS T1 WHERE T1.ColumnA = " & [T].[ColumnA]
   • Initial value: "" (empty string)
   • Concatenation separator: ", " (comma and space)

3. The GROUP BY clause groups records by ColumnA to ensure that concatenated values ​​are grouped correctly.

The above is the detailed content of How Can I Concatenate Rows in MS Access Based on a Grouping Column?. For more information, please follow other related articles on the PHP Chinese website!