31

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?

reggie
  • 13,313
  • 13
  • 41
  • 57
  • 1
    1) Standard SQL has no `Concat` set function: what would the resulting data type be? would it violate 1NF? would it be a set? how could such data be queried? etc; 2) Access2007 introduced multi-valued types (http://www.theregister.co.uk/2006/07/18/multivalued_datatypes_access/print.html); 3) Have you considered a report? The one that ships with Access is quite nice. – onedaywhen Apr 04 '11 at 13:17

4 Answers4

27

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;
Community
  • 1
  • 1
Thomas
  • 63,911
  • 12
  • 95
  • 141
9

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

Deborah Cole
  • 2,876
  • 3
  • 19
  • 19
6

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.

Patrick
  • 7,512
  • 7
  • 39
  • 50
  • Yeah. I would really appreciate if you could post that solution for me. – reggie Apr 01 '11 at 18:36
  • 1
    @reggie - The problem with the above solution is that unknown number of child item.s Right now, each item only has 2 column B values. What happens when there are 10? 100? – Thomas Apr 01 '11 at 18:44
  • ultimately.. its determined by how many items you plan on having... if columnA value can have 20 items grouped in columnB... this is probably not your best solution... if your looking at 2 - 3 its possible. – Patrick Apr 01 '11 at 18:49
  • @Thomas.. hence the limitation. in practice I have a 3rd item that iif the count is more than two it displays "more..." but it all is determined by the OP's needs and requirements. If he can't use a function for some reason or another... he cant use a function.. and also as mentioned... if he has more than 3 or 4 its not a good solution. – Patrick Apr 01 '11 at 18:49
  • @Patrick - Agreed. Not demeaning your solution but rather the approach requested by the OP. – Thomas Apr 01 '11 at 18:51
  • No offense taken... I agree entirely... Access isnt tsql or plsql... I wish it was though.. it would make my life so much easier. – Patrick Apr 01 '11 at 18:52
  • In all honesty... I like your solution better, and I will probably implement it in the near future. +1 – Patrick Apr 01 '11 at 18:55
  • Could you please explain how to do the same with 3 items ? **Choose** function not working here @Patrick – MujtabaFR Jan 13 '20 at 14:34
2

The table could have a sequence column, which gives it a unique primary key of ColumnA-sequence:

table: t1
ColumnA sequence ColumnB
1       1        abc
1       2        pqr
1       3        xyz
2       1        efg
2       2        hij
3       1        asd

And a Crosstab could be created:

query: x1
TRANSFORM Min([columnB] & ", ") AS Expr1
SELECT t1.columnA
FROM t1
GROUP BY t1.columnA
PIVOT t1.sequence;

columnA 1    2    3
1       abc, pqr, xyz,
2       efg, hij,
3       asd,

Then a final query can combine the columns and remove the last comma:

SELECT x1.columnA, Left([1] & [2] & [3],Len([1] & [2] & [3])-2) AS columnB FROM x1;

columnA columnB
1       abc, pqr, xyz
2       efg, hij
3       asd

To automate filling in the sequence, the following VBA code can be used:

Sub fill_sequence_t1()
  Dim i: i = 1
  Do While DCount("*", "t1", "sequence IS NULL") > 0
    DoCmd.RunSQL "SELECT t1.columnA, Min(t1.columnB) AS columnB_min INTO t2" & _
                 " FROM t1 WHERE t1.sequence IS NULL GROUP BY t1.columnA;"
    DoCmd.RunSQL "UPDATE t1 INNER JOIN t2 ON (t1.columnA = t2.columnA)" & _
                 " AND (t1.columnB = t2.columnB_min) SET t1.sequence=" & i
    CurrentDb.TableDefs.Delete "t2"
    i = i + 1
  Loop
End Sub
Will
  • 1,048
  • 9
  • 10