How can I get a SQL query to aggregate lists of results that correspond to the same name?

Question

I was given the beginning of a SQL Query and am trying to adjust it to get the results we want. I have a large dataset that I want to have one line for each name and merchant id, and for the remaining 2 columns, I want the results to be in a list. After looking on the internet for a while, it seemed that listagg might be my best bet (string_agg did not come up as a function when i tried it). I am very new to SQL and still have a lot to learn, so I could be missing something super obvious, and I apologize for that.

For example, I have :

name	account_id	category_name	class_name
jen	1234	google	commerce
jen	1234	shopify	site
jen	1234	ads	marketing
sam	098	google	feedback
sam	098	shopify	email
sam	098	facebook	spend

I would like it to appear as:

name	account_id	category_name	class_name
jen	1234	google; shopify; ads	commerce; site; marketing
sam	098	google; shopify; facebook	feedback; email; spend

My current query is:

SELECT
 a.name AS merchant_name
, a.account_id AS merchant_id
, listagg( ic.category_name, ';') AS category
, listagg( ai.class_name, ';') AS integration_name
FROM metrics.account_integrations ai
INNER JOIN metrics.accounts a ON ai.account_id = a.account_id
LEFT JOIN metrics.integration_categories ic ON ai.class_name = ic.integration_name
LEFT JOIN metrics.account_targets atar ON ai.account_id = atar.account_id
GROUP BY 1,2, ic.category_name, ai.class_name, a.name
ORDER BY a.name;

It is currently creating multiple lines for names and ids based on each distinct category and class_name. Where am I going wrong?

Answer 1

We only need to group by name and account id.

select   name
        ,account_id
        ,listagg(category_name, '; ') as category_name
        ,listagg(class_name, '; ')    as class_name
from     t
group by name, account_id
order by name

NAME	ACCOUNT_ID	CATEGORY_NAME	CLASS_NAME
jen	1234	google; shopify; ads	commerce; site; marketing
sam	98	google; shopify; facebook	feedback; email; spend

Fiddle