How to populate a PostgresQL column with a list of all values from column1 from all rows with the current row’s value of column2

Question

I have a Postgres table with the following format:

Fruit	Owner
Apple	John
Orange	Susan
Pear	Michael
Peach	Susan

I want to write a query that creates a new column, Owner's Fruits, which lists all fruits owned by the owner of the row in question, like this:

Fruit	Owner	Owner's Fruits
Apple	John	Apple
Orange	Susan	Orange, Peach
Pear	Michael	Pear
Peach	Susan	Orange, Peach

However, I am unable to figure out how to do this, nor even how to phrase my question so that I find an answer on here or Google.

It seems like it should be simple enough. How can I do this?

Answer 1

You can use string_agg():

select t.*,
       string_agg(fruit, ',') over (partition by owner) as owners_fruits
from t;

That said, I actually recommend putting the values into an array instead:

select t.*,
       array_agg(fruit) over (partition by owner) as owners_fruits
from t;

Arrays are more versatile.