how do I pivot this on google BigQuery?

Question

This is the data that I am currently working with.

x	y	a
3	2	LL
5	2	LL
5	4	LL
3	4	LL
6	7	RR
8	7	RR
8	9	RR
6	9	RR

I am trying to pivot the table such that it becomes:

x1	x2	y1	y2	a
3	5	2	4	LL
6	8	7	9	RR

I've tried AGG and the PIVOT functions, but can't seem to get this to work, and this has to be done using onky Google BQ. The complete dataset is much larger, so I need a general solution. Any help or suggestions would be appreciated. Thanks!

Does this solution with pivot works for you? https://stackoverflow.com/questions/26272514/how-to-pivot-table-in-bigquery — sagi, Jan 30 '23 at 10:02

score 0 · Answer 1 · answered Feb 01 '23 at 06:33

Below should be good start for you

select * from (
  select * from 
  (select *, row_number() over(partition by a) pos from (select distinct x, a from your_table)) 
  full outer join 
  (select *, row_number() over(partition by a) pos from (select distinct y, a from your_table)) 
  using (a, pos)
)
pivot (any_value(x) as x, any_value(y) as y for pos in (1,2))

if applied to sample data in your question - output is

you can build that query dynamically (so you will not need to explicitly specify count of unique values) and use EXECUTE IMMEDIATE to run it - there are plenty examples of such technique here on SO!

how do I pivot this on google BigQuery?

1 Answers1