Ms Access Query: Concatenating Rows through a query

Suppose I have table in Ms Access with following information:

ColumnA ColumnB
1       abc
1       pqr
1       xyz
2       efg
2       hij
3       asd

My question is, how can I concatenate the values in the second column to a row value based on the first column. The query results that I want is as follows:

ColumnA ColumnB
1       abc, pqr, xyz
2       efg, hij
3       asd

I want to achieve this through a query. Can someone help me attain this?

Solution 1:

You need a function to do the concatenation.

Microsoft Access condense multiple lines in a table

Example using your data:

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;

Solution 2:

Here's an outstanding link re: how to do this from within SQL by calling a function. The instructions are exceptionally clear & the function is written for you so you can just copy, paste & go. Even someone with no knowledge of VB can easily implement it: Concatenate values from related records

Solution 3:

this can be very difficult to obtain. If you MUST do it in a query and not a function, the problem that you will run into is the limit of the number of rows you can concatenate into one column. So far the only way that i have found to achieve this is via iif statements.

SELECT 
test1.ColumnA AS ColumnA, 
First([test1].[ColumnB]) & IIf(Count([test1].[ColumnB])>1,"," & Last([test1].[ColumnB])) AS ColumnB
FROM test1
GROUP BY test1.ColumnA;

returns:

ColumnA  ColumnB 
1      abc,xyz 
2      efg,hij 
3      asd

This will return the first and the last only, but I'm sure with a little work you could work out the Choose function, but like I said you would have to add more iif statements for each additional item you want to add, hence the limitation.