bigquery sql: converting column values into column names [duplicate]

Question

I would like to convert all the keys into column headers and all the values into respective average values in a row underneath, grouped by date.

This is the query I have:

select 
substr(CAST((DATE) AS STRING),0,8) as daydate,
split(x,':')[safe_offset(0)] as key, 
cast(split(x,':')[safe_offset(1)] as float64) as value
from `gdelt-bq.gdeltv2.gkg_partitioned`, 
unnest(split(GCAM, ',')) as x
where _PARTITIONTIME BETWEEN TIMESTAMP('2019-02-02') AND TIMESTAMP('2019-02-03')

Is there a way to do this in bigquery standard sql?

Many thanks

Answer 1

It depends on how many distinct keys you have? If you are talking about a few thousand (or even tens or hundreds of distinct keys) then your data design is not very ideal. But, if it's definitive and manageable, you can create a bunch of IF conditions to convert your keys into columns.

Let's say you only have 3 distinct keys, then you can do something like:

select 
   daydate,
   key,
   sum(if(key = 'x', value, 0)) as val_x,
   sum(if(key = 'y', value, 0)) as val_y,
   sum(if(key = 'z', value, 0)) as val_z
from (
   select 
      substr(CAST((DATE) AS STRING),0,8) as daydate,
      split(x,':')[safe_offset(0)] as key, 
      avg(cast(split(x,':')[safe_offset(1)] as float64)) as value
   from `gdelt-bq.gdeltv2.gkg_partitioned`, unnest(split(GCAM, ',')) as x
   where _PARTITIONTIME BETWEEN TIMESTAMP('2019-02-02') AND TIMESTAMP('2019-02-03')
   group by 1,2
)
group by 1,2

UPDATE: You can adapt a more robust, practical and better solution:

select 
      substr(CAST((DATE) AS STRING),0,8) as daydate,
      split(x,':')[safe_offset(0)] as key, 
      avg(cast(split(x,':')[safe_offset(1)] as float64)) as value
from `gdelt-bq.gdeltv2.gkg_partitioned`, unnest(split(GCAM, ',')) as x
where _PARTITIONTIME BETWEEN TIMESTAMP('2019-02-02') AND TIMESTAMP('2019-02-03')
group by 1,2