CODEWITHSUNDEEP
sqlgoogle-bigquery
user11035754
user11035754

Reputation: 227

Combine multiple records into one based on a condition

I want to combine each product_code's (comma-separated) in a single entry/record if all other values in multiple records are the same except the product_code

The dataset looks like below-

category_id subcat_id  product_code customer_id  quantity   value
   123        456        AB            111          2         1
   123        456        CD            111          2         1
   123        789        AB            111          2         2
   123        789        CD            111          2         2

The result should look like-

category_id subcat_id  product_code   customer_id    quantity   value
   123        456        AB,CD             111          2         1
   123        789        AB,CD             111          2         2

Upvotes: 0

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Use string_agg();

select category_id, subcat_id, customer_id, quantity, value,
       string_agg(product_code, ',')
from t
group by category_id, subcat_id, customer_id, quantity, value;

That said, I recommend arrays instead of strings for storing such values.

Upvotes: 1

Related Questions