CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerypivot-table
hieuiph
hieuiph

Reputation: 51

create new columns from row in Bigquery

I have a table like below that presents the carts of products

enter image description here

every id has different products, example: id 1 has 3 products, id 2 has 4...but the maximum products for each id is 10. the timestamp is sorted in DESC. so now I'd like to transform this table like this:

enter image description here

the schema of table:

enter image description here

I tried the solution here Pivot Repeated fields in BigQuery but the number of columns are too many. How can I have new columns corresponds with maximum 10 products

thank you

Upvotes: 1

Views: 506

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Below options are for BigQuery Standard SQL 

#standardSQL
SELECT id, 
  products[SAFE_OFFSET(0)].product_id product_id_1,
  products[SAFE_OFFSET(0)].price price_1,
  products[SAFE_OFFSET(1)].product_id product_id_2,
  products[SAFE_OFFSET(1)].price price_2,
  products[SAFE_OFFSET(2)].product_id product_id_3,
  products[SAFE_OFFSET(2)].price price_3,
  products[SAFE_OFFSET(3)].product_id product_id_4,
  products[SAFE_OFFSET(3)].price price_4,
  products[SAFE_OFFSET(4)].product_id product_id_5,
  products[SAFE_OFFSET(4)].price price_5,
  products[SAFE_OFFSET(5)].product_id product_id_6,
  products[SAFE_OFFSET(5)].price price_6,
  products[SAFE_OFFSET(6)].product_id product_id_7,
  products[SAFE_OFFSET(6)].price price_7,
  products[SAFE_OFFSET(7)].product_id product_id_8,
  products[SAFE_OFFSET(7)].price price_8,
  products[SAFE_OFFSET(8)].product_id product_id_9,
  products[SAFE_OFFSET(8)].price price_9,
  products[SAFE_OFFSET(9)].product_id product_id_10,
  products[SAFE_OFFSET(9)].price price_10
FROM `project.dataset.table`

or 

#standardSQL
SELECT id, 
  MAX(IF(off = 0, product_id, NULL)) product_id_1,
  MAX(IF(off = 0, price, NULL)) price_1,
  MAX(IF(off = 1, product_id, NULL)) product_id_2,
  MAX(IF(off = 1, price, NULL)) price_2,
  MAX(IF(off = 2, product_id, NULL)) product_id_3,
  MAX(IF(off = 2, price, NULL)) price_3,
  MAX(IF(off = 3, product_id, NULL)) product_id_4,
  MAX(IF(off = 3, price, NULL)) price_4,
  MAX(IF(off = 4, product_id, NULL)) product_id_5,
  MAX(IF(off = 4, price, NULL)) price_5,
  MAX(IF(off = 5, product_id, NULL)) product_id_6,
  MAX(IF(off = 5, price, NULL)) price_6,
  MAX(IF(off = 6, product_id, NULL)) product_id_7,
  MAX(IF(off = 6, price, NULL)) price_7,
  MAX(IF(off = 7, product_id, NULL)) product_id_8,
  MAX(IF(off = 7, price, NULL)) price_8,
  MAX(IF(off = 8, product_id, NULL)) product_id_9,
  MAX(IF(off = 8, price, NULL)) price_9,
  MAX(IF(off = 9, product_id, NULL)) product_id_10,
  MAX(IF(off = 9, price, NULL)) price_10
FROM `project.dataset.table`,
UNNEST(products) product WITH OFFSET off
GROUP BY id

Upvotes: 5

Related Questions