Multi-level pivot in Google BigQuery

Question

Is it possible to do the following pivot in one query using BigQuery, or would I need to break it up into multiple queries?

Here is the raw data:

And here is the Pivot Table:

Is there a way to construct an arbitrarily-nested Pivot Table in BigQuery? Or does each level need its own SQL Query?

Note, in the above, it would be simple to do a CASE WHEN statement for each of the 6 column combinations (CA-M, CA-F, FR-M, FR-F, US-M, US-F), but for the sake of the general case, let's suppose there may be hundreds of countries so it's not practical to manually write in each combination as a different CASE statement.

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2017-02-17T01:01:27

Below is most optimal in my mind option

Step 1 - prepare query based on your data

#standardSQL
WITH country_sex_list AS (
  SELECT Country, Sex 
  FROM yourTable 
  GROUP BY Country, Sex
),
permutations AS (
SELECT 
  STRING_AGG(CONCAT("SUM(CASE WHEN (Country, Sex) = ('", Country, "', '", Sex, "') THEN Income END) AS ", Country, "_", Sex), ',' ORDER BY Country, Sex) AS text
FROM country_sex_list 
)
SELECT 
  CONCAT(
    "SELECT company, ", text, ", SUM(Income) AS Total FROM yourTable GROUP BY Company UNION ALL ",
    "SELECT 'Total' as company, ", text, ", SUM(Income) AS Total FROM yourTable"
  ) AS query
FROM permutations

Step 2 - take text of result of Step 1 and run it as a query.
Result will be as you expect (see example below)

company   CA_M    FR_F    FR_M    US_F    US_M    Total  
Acme      null  40,000    null    null  40,000   80,000  
Bravo   50,000    null    null  30,000    null   80,000  
Delta     null    null  40,000    null    null   40,000  
Total   50,000  40,000  40,000  30,000  40,000  200,000

I think these two steps are generic enough to extend to real use-case

Of course, You can run those two steps manually in Web UI or you can script them in client of your choice

Below is dummy data to test with

WITH yourTable AS (
  SELECT 'M' AS Sex, 'US' AS Country, 40000 AS Income, 'Acme' AS Company UNION ALL
  SELECT 'M', 'CA', 50000, 'Bravo' UNION ALL
  SELECT 'F', 'US', 30000, 'Bravo' UNION ALL
  SELECT 'F', 'FR', 40000, 'Acme' UNION ALL
  SELECT 'M', 'FR', 40000, 'Delta'
)

Multi-level pivot in Google BigQuery

1 Answers