Multiple STRING_AGG in one query

Question

I have a dataset that looks like this:

Dummy table

The desired view I'd like is:

enter image description here

I have tried the STRING_AGG function, which works, but for only one column. See below for my example SQL statement (using GCP):

CREATE OR REPLACE TABLE dummy_table AS SELECT a.order, STRING_AGG(products,', ') products_added FROM [table with orders] a INNER JOIN [table with orders, products and actions] b ON a.order = b.order WHERE b.action = 'Add' GROUP BY a.order ;

This gives me partially what I want, which is an orders column, then a products added string separated by a comma. However, how can I also build in the update and delete products in the same query?

Answer 1

You can achieve this result by creating and joining three subqueries, like:

select t1.order, t1.products_added, t2.products_updated, t3.products_deleted
from (SELECT a.order, STRING_AGG(products,', ') products_added FROM [table with orders] a INNER JOIN [table with orders, products and actions] b ON a.order = b.order WHERE b.action = 'Add' GROUP BY a.order) t
left join (SELECT a.order, STRING_AGG(products,', ') products_updated FROM [table with orders] a INNER JOIN [table with orders, products and actions] b ON a.order = b.order WHERE b.action = 'Update' GROUP BY a.order) t2
on t1.order = t2.order
left join (SELECT a.order, STRING_AGG(products,', ') products_deleted FROM [table with orders] a INNER JOIN [table with orders, products and actions] b ON a.order = b.order WHERE b.action = 'Delete' GROUP BY a.order) t3
on t1.order = t3.order