BigQuery pivot multiple rows into columns

Question

I have a table similar to this in BigQuery.

Accounts	ACount	BCount	Date
A	4	5	2021-12-08
A	3	4	2021-12-07
B	2	3	2021-12-08
B	4	6	2021-12-07

I need to convert this table to something similar to below.

Accounts	ACount_12-07	BCount_12-07	ACount_12-08	BCount_12-08
A	3	4	4	5
B	4	6	2	3

Can BigQuery experts help me figure out how to achieve this through BigQuery?

Thank you in advance,

user_5221944 · Answer

Consider below approach

select * from your_table
pivot (
  any_value(ACount) as ACount, 
  any_value(BCount) as ACount 
  for replace(Date, '-', '_') in ('2021_12_07', '2021_12_08')
)

if applied to sample data in your question - output is

Hope, you can use it in your real use-case

BigQuery pivot multiple rows into columns

1 Answers