CODEWITHSUNDEEP
google-bigquery
Pavitra Jayaraman
Pavitra Jayaraman

Reputation: 13

BigQuery Partition according to Category

Need some help in solving this. Say There is a table x with 2 fields -> order number and category. For a single order number, there can be more than one category (max kind is 3 say).

tx_num Category

1234 blade

1234 razor

12345 blade

12345 book

123456 blade

1234 book

I want to return something like this using big query

tx_num type

1234 contains blade, razor, book

12345 contains blade and book

123456 contains only blade

Upvotes: 1

Views: 57

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below si for BigQuery Standard SQL

#standardSQL
select tx_num, 
  'contains ' || string_agg(Category, ', ') type
from `project.dataset.table`
group by tx_num

If to apply to sample data from your question - output is

enter image description here

Upvotes: 2

Related Questions