CODEWITHSUNDEEP
sqldatabasegoogle-bigquery
Govinda Totla
Govinda Totla

Reputation: 616

Pivot rows in BigQuery

How do I pivot in BigQuery?

Suppose my table is like - 

id   event 
----------
1    type 1    
1    type 2    
2    type 2    
2    type 2    
2    type 2     
3    type 1

I want to query something like this - 

id type1 type2
----------
1   1    1     
2   0    3
3   1    0

Upvotes: 0

Views: 417

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need conditional sum()

select id, sum(case when event = 'type 1' then 1 else 0 end) as type1,
           sum(case when event = 'type 2' then 1 else 0 end) as type2
from table t
group by id;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use conditional aggregation:

select id,
       sum(case when event = 'type 1' then 1 else 0 end) as type1,
       sum(case when event = 'type 2' then 1 else 0 end) as type2
from t
group by id;

Upvotes: 2

Related Questions