How to "unpivot" a table in BigQuery
Recently, I was presented with a problem in BigQuery: How do I make a table's metrics be returned as rows? Normally, metrics are presented as columns and dimensions as rows. But in this case, I needed the metrics to appear as rows too. In short, I needed to "unpivot" a table like this: Dimension1 Dimension2 Metric1 Metric2 Metric3 to this: Dimension1 Dimension2 Metric Data Metric1 Metric2 Metric3 Metric1 Metric2 Metric3 Metric1 Metric2 Metric3 The solutions lay with three BigQuery features: STRUCT type ARRAY s CROSS JOIN with the UNNEST operator How to get unpivoted results in BigQuery Let's say I have data like this in a table called "Campaign_Results": Row Superhero Title Date Impressions Clicks Conversions 1 Superman Man of Steel 2013-06-14 5670096 796191 82538 2 Batman Batman v Superman: Dawn of Justice 2016-03-25 9742525 876292 63812 3 Wonder Woman Wonder Woman 2017-06-02 4847506 420674 23428 Bu