Google Spreadsheets: How do you concat strings in an aggregation function

Question

Say I have a table:

A, 1
B, 1
C, 2
D, 1
E, 2

How do I view the table grouping by the 2nd column and aggregating by the first with a comma separated concat function ie:

1, "A,B,D"
2, "C,E"

In both defining a pivot table and using the QUERY syntax, it seems that the only aggregation functions available are numerical aggregations like MIN, MAX, SUM, etc. Can I define my own aggregation function?

Answer 1

You have to add a "Calculated Field" to the pivot table, and then select "Summarise by > Custom". This will make the column names in your formula refer to an array of values (instead of a single value). Then you can type a formula like:

= JOIN(", ", MyStringColumn)

More specifically, if you have the following table:

Create a pivot table by going to "Data > Pivot table", with the following configuration. Ensure "Summarize by" is set to "Custom"!

Answer 2

Another option: if the data is in A2:B, then, say, in D2:

=UNIQUE(B2:B)

and then in E2:

=JOIN(",",FILTER(A$2:A,B$2:B=D2))

which is filled down as required.

There are one-formula, auto-expanding solutions, although they get quite convoluted.