Pivot table data using GROUP BY ROLLUP-CodePudding

[Note: any SQL database that supports ROLLUP can be used here. I've used BigQuery for the examples.]

I am looking to get all the data for the following PivotTable in Excel:

There should be 36 cells of data. The BigQuery query that I have so far is as follows:

SELECT Year, Quarter, ProductGroup, Product, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ROLLUP(ProductGroup, Product, Year, Quarter)
union distinct
SELECT Year, Quarter, ProductGroup, Product, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ROLLUP(Year, Quarter, Product, ProductGroup)

However, it gives me only 34 results, whereas I'm looking for 36. What would be the proper way to provide all the pivot-table combinations in this query? Data attached here:

$ cat > Products.csv
ProductGroup,Product,Year,Quarter,Revenue,Units,Count,Product Key,Reseller,Product Info,QuarterAsNumber
Electronics,Phone,2018,Q1,103,7,1,2018-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2018,Q1,102,4,1,2018-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2019,Q1,98,12,1,2019-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Computer,2018,Q1,104,3,1,2018-Q1,Samsung,Format=XML; <Properties>…,1
Electronics,Computer,2019,Q1,83,7,1,2019-Q1,Google,Format=XML; <Properties>…,1
Media,Theater,2018,Q1,17,4,1,2018-Q1,Sony,Format=XML; <Properties>…,1
Media,Theater,2019,Q1,20,7,1,2019-Q1,Sony,Format=XML; <Properties>…,1
Media,Movies,2018,Q1,25,12,1,2018-Q1,Microsoft,Format=XML; <Properties>…,1
Media,Movies,2019,Q1,26,13,1,2019-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2018,Q2,105,5,1,2018-Q2,Samsung,Format=XML; <Properties>…,2
Electronics,Phone,2019,Q2,82,15,1,2019-Q2,LG,Format=XML; <Properties>…,2
Electronics,Computer,2018,Q2,99,4,1,2018-Q2,LG,Format=XML; <Properties>…,2
Electronics,Computer,2019,Q2,84,20,1,2019-Q2,Sony,Format=XML; <Properties>…,2
Media,Theater,2018,Q2,17,4,1,2018-Q2,Microsoft,Format=XML; <Properties>…,2
Media,Theater,2019,Q2,22,5,1,2019-Q2,Sony,Format=XML; <Properties>…,2
Media,Movies,2018,Q2,25,12,1,2018-Q2,Samsung,Format=XML; <Properties>…,2
Media,Movies,2019,Q2,26,14,1,2019-Q2,Google,Format=XML; <Properties>…,2
Electronics,Phone,2000,Q1,103,7,1,2000-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2001,Q1,102,4,1,2001-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2002,Q1,98,12,1,2002-Q1,Microsoft,Format=XML; <Properties>…,1
Electronics,Computer,2003,Q1,104,3,1,2003-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Computer,2004,Q1,83,7,1,2004-Q1,Samsung,Format=XML; <Properties>…,1
Media,Theater,2005,Q1,17,4,1,2005-Q1,Google,Format=XML; <Properties>…,1
Media,Theater,2006,Q1,20,7,1,2006-Q1,Sony,Format=XML; <Properties>…,1
Media,Movies,2007,Q1,25,12,1,2007-Q1,Sony,Format=XML; <Properties>…,1
Media,Movies,2008,Q1,26,13,1,2008-Q1,Microsoft,Format=XML; <Properties>…,1
Electronics,Phone,2009,Q2,105,5,1,2009-Q2,Sony,Format=XML; <Properties>…,2
Electronics,Phone,2010,Q2,82,15,1,2010-Q2,Sony,Format=XML; <Properties>…,2
Electronics,Computer,2011,Q2,99,4,1,2011-Q2,Sony,Format=XML; <Properties>…,2
Electronics,Computer,2012,Q2,84,20,1,2012-Q2,Sony,Format=XML; <Properties>…,2
Media,Theater,2013,Q2,17,4,1,2013-Q2,Sony,Format=XML; <Properties>…,2
Media,Theater,2014,Q2,22,5,1,2014-Q2,Sony,Format=XML; <Properties>…,2
Media,Movies,2015,Q2,25,12,1,2015-Q2,Sony,Format=XML; <Properties>…,2
Media,Movies,2016,Q2,26,14,1,2016-Q2,Samsung,Format=XML; <Properties>…,2
Media,Movies,2017,Q1,26,13,1,2017-Q1,Google,Format=XML; <Properties>…,1
Electronics,Phone,2018,Q2,105,5,1,2018-Q2,Sony,Format=XML; <Properties>…,2
Electronics,Phone,2019,Q2,82,15,1,2019-Q2,Sony,Format=XML; <Properties>…,2
Electronics,Computer,2020,Q2,99,4,1,2020-Q2,Microsoft,Format=XML; <Properties>…,2
Electronics,Phone,2020,Q1,103,7,1,2020-Q1,Sony,Format=XML; <Properties>…,1
Electronics,Phone,2020,Q2,102,4,1,2020-Q2,Samsung,Format=XML; <Properties>…,2
Electronics,Phone,2020,Q3,98,12,1,2020-Q3,LG,Format=XML; <Properties>…,3
Electronics,Computer,2020,Q4,104,3,1,2020-Q4,LG,Format=XML; <Properties>…,4
Electronics,Computer,2020,Q1,83,7,1,2020-Q1,Sony,Format=XML; <Properties>…,1
Media,Theater,2020,Q1,17,4,1,2020-Q1,Microsoft,Format=XML; <Properties>…,1
Media,Theater,2020,Q1,20,7,1,2020-Q1,Sony,Format=XML; <Properties>…,1

Here is the long-form of what I am looking for:

-- >, ProductGroup> 
SELECT ProductGroup, NULL, NULL, NULL, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ROLLUP (ProductGroup)

-- ProductGroup>Product
union distinct 
SELECT ProductGroup, Product, NULL, NULL, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ProductGroup, Product

-- ProductGroup>Product>Year
union distinct 
SELECT ProductGroup, Product, Year, NULL, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ProductGroup, Product, Year

-- ProductGroup>Year
union distinct 
SELECT ProductGroup, NULL, Year, NULL, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ProductGroup, Year

-- ProductGroup>Year>Quarter
union distinct 
SELECT ProductGroup, NULL, Year, Quarter, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ProductGroup, Year, Quarter

-- ProductGroup>Product>Year>Quarter
union distinct 
SELECT ProductGroup , Product, Year, Quarter, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY ProductGroup, Product, Year, Quarter

-- Year>Quarter
union distinct 
SELECT NULL , NULL, Year, Quarter, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY Year, Quarter

-- Year
union distinct 
SELECT NULL , NULL, Year, NULL, SUM(Revenue) AS SumOfRevenue, SUM(Units) as SumOfUnits FROM `first-outlet-750.biengine_tutorial.Product`
WHERE Year IN (2020) and Quarter in ('Q1')
GROUP BY Year

CodePudding user response：

BigQuery does not have Cube or Grouping Set, so below trick is what you can use - looks quite generic to me to be expanded to any dimensions while avoiding redundant lines of code

select 
  (case when grp_set & 1 > 0 then ProductGroup end) as ProductGroup,
  (case when grp_set & 2 > 0 then Product end) as Product,
  (case when grp_set & 4 > 0 then Year end) as Year,
  (case when grp_set & 8 > 0 then Quarter end) as Quarter,
  sum(Revenue) as Revenue,
  sum(Units) as Units    
from `first-outlet-750.biengine_tutorial.Product`, unnest(generate_array(1, 16)) grp_set
where Year IN (2020) and Quarter in ('Q1')
group by 1, 2, 3, 4
having not (Year is null and not Quarter is null)
and not (ProductGroup is null and not Product is null)
-- order by 1, 2, 3, 4

if applied to sample data in your question - output is