0

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?

hmd39
  • 3
  • 2
  • Quoted from the [sql] tag description: _"Structured Query Language (SQL) is a language for querying databases. Questions should include [...] a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific DBMS (uses specific extensions/features), use that DBMS's tag instead. [...]"_ – Stefan Wuebbe Oct 14 '22 at 09:44

1 Answers1

0

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

DannySlor
  • 4,574
  • 1
  • 3
  • 11