How to Query Google Sheet to Group Column Data Many to One

Question

I have a sheet with property managers and the id of the properties they manage:

pid     name        email  

34721   Aron Hall   ahall@test.com
35173   Aron Hall   ahall@test.com
35057   Bobby Blue  bb@test.com
34689   Bobby Blue  bb@test.com
34621   Bobby Blue  bb@test.com

Is it possible to query into a new sheet tab each property manager and all the properties associated with them as one record instead of a duplicate record for each property a manager has ?

I'd like the new sheet to have rows like so:

name        email           pid
Aron Hall   ahall@test.com  34721, 35173
Bobby Blue  bb@test.com     35057, 34689, 34621

I know Google has its own query language but I'm not sure if my request if possible, if so what query functions might I use ?

Answer 1

Assuming you have the information above starting at column A row 1 you would use the following:

=JOIN(", ",QUERY(A2:C6,"select A where B = 'Aron Hall'"))

Replace the name with whatever name you are looking to check.

JOIN is similar to the concatenate function, the difference being that it will add whatever delimiter you choose between the string values. In the case above it is set to ", "

QUERY returns a series of valuesfrom the range you select, A2:C6 but this could be modified to SheetName!A2:C6 if you are working from another tab.

The way it is set up above returns the value in column "A" in each row where the value in "B" in that same row is equal to Aron Hall.

EDIT** Additional functionality as requested:

On your new tab where you are displaying the manager names you can utilize

=UNIQUE(B2:B)

Simply swap B2:B for whatever column your manager names are in, this will populate a list below the cell you insert that function into with only one instance of each managers name.

To update the query based on the managers name in the row you can use

=JOIN(", ",QUERY($A$2:$B,"select A where B='"&$F2&"'"))

You will need to change the range $A$2:$B to the appropriate range, and change the select based on where your information is. F2 just happened to be where I had the managers name populated, so you will want that to reference whatever cell the =UNIQUE formula drops their name.