Merge rows that are identical except for the last column in SQL, squashing the unique values in the result

Question

Imagine I own a pet store and have Customers and Pets tables.

If I did a query like

SELECT
CUSTOMER.ID,
CUSTOMER.NAME,
CUSTOMER.EMAIL,
PET.NAME
FROM CUSTOMERS
INNER JOIN PETS ON PET.CUSTOMER_ID = CUSTOMER.ID

I should be getting results like

[1, 'John Doe', 'johndoe@jd.com', 'Bailey']

But if I had customers with multiple pets, that means I'd be getting multiple rows:

[1, 'John Doe', 'johndoe@jd.com', 'Bailey'],
[1, 'John Doe', 'johndoe@jd.com', 'Luna']

Is there a way for me to somehow condense these rows yet retain the unique values? How would I be able to get a result like

[1, 'John Doe', 'johndoe@jd.com', ['Bailey', 'Luna']]

?

I tried to SELECT DISTINCT Customer IDs, but I think that only works if that's the only field you're selecting? When I add in the rest of of the fields to select, I still get duplicate rows

Answer 1

You can use string_agg function to get your desired result -

SELECT c.ID, c.NAME, c.EMAIL,
       STRING_AGG(p.NAME, ',')
  FROM CUSTOMERS c
 INNER JOIN PETS p ON p.CUSTOMER_ID = c.ID
 GROUP BY c.ID, c.NAME, c.EMAIL;