Search code examples
sqlgoogle-bigquerypivottransposeunpivot

GBQ Transpose Table in SQL

Trying to transpose a table in GBQ. The solution needs to be able to work dynamically with varying columns and rows. I know there is a way to do this by listing out columns specifically, but I have varying tables with large numbers of rows. There is an unpivot function in GBQ now and my idea was to Unpivot, then Pivot it. Not sure if anyone has dealt with this yet?

Starting Data

Result

enter image description here

Solution

  • Consider below approach

    select * from (
  select * from data
  unpivot (value for Fruit in (Apple, Bananas, Orange))
)
pivot (max(value) for `Group` in ('North', 'South', 'West'))

    If applied to sample data in your question - output is

    enter image description here

    Below version builds all references needed for unpivot and pivot dynamically on fly

    execute immediate (select '''select * from (
  select * from `project.dataset.table`
  unpivot (value for Fruit in (''' || (select array_to_string(regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":'), ', ')
from `project.dataset.table` t limit 1) || ''')) 
)
pivot (max(value) for `Group` in (''' || (select '"' || string_agg(distinct `Group`, '", "' order by `Group`) || '"' from `project.dataset.table`) || '''))
'''
);