sql - Multiple STRING_AGG in one query - Stack Overflow

admin管理员组

文章数量:1122826

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?

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?

• sql
• google-cloud-platform
• aggregate
• string-aggregation

Share Improve this question Follow asked Nov 21, 2024 at 10:54 user28412706user28412706 311 bronze badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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

本文标签： sqlMultiple STRINGAGG in one queryStack Overflow