pivot function in Bigquery for transpose, wrong value falling in

Question

I'm trying to use pivot function to transpose rows however action_type=4 keeps falling to the wrong column after I ran my query. Below is the sample data:

SessionId	action_type	products
122	3	5
122	4	1
127	3	2
189	4	1

Ideal output will look like below:

SessionId	action_type_1	products_1	action_type_2	products_2
122	3	5	4	1
127	3	2
189			4	1

I have written below query trying to do the transpose:

select * from
(select * except (SessionId),
max(SessionId) over win SessionId,
row_number() over (win order by SessionId, action_type, products) tab

from 
`xxx.sample.xxx` 
window win as (partition by SessionId) 
)
pivot (
any_value (action_type) as action_type ,
any_value(products) as products for tab in (1,2))

However this output has returning some strange results, for example I see value 4 under action_type_1, which is not what I expected. action_type_1 should only have value 3 because I wanted to define action_type_1=3 and action_type_2=4. Can anyone help look at my query? Any advises are appreciated!

Mikhail Berlyant · Answer

I think, below is what you are looking for

select * from your_table
pivot (
  any_value (action_type) as action_type ,
  any_value(products) as products 
  for action_type in (3,4)  
)

with output

so, as you can see - instead of relying on offset - you just simply go directly off of action type!

In case if for some reason you need output as _1 and _2 - use below trick

select * from your_table
pivot (
  any_value (action_type) as action_type ,
  any_value(products) as products 
  for case action_type when 3 then 1 when 4 then 2 end in (1,2)  
)

with output

pivot function in Bigquery for transpose, wrong value falling in

1 Answers