A detailed tutorial
Pivot is a very simple function in BigQuery that can be very useful when you need to rotate rows into columns. It uses an aggregate function on rows and converts the categories in rows to columns. There is unpivot function that does totally an opposite operation. If you are a big query user in Google Cloud Platform and haven’t used Pivot and Unpivot functions yet, it is worth learning.
This article will focus on explaining Pivot and Unpivot functions with examples. For this We will create a dummy table as follows:
WITH Produce AS (SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter, 2020 as year UNION ALLSELECT 'Kale', 23, 'Q2', 2020 UNION ALLSELECT 'Kale', 45, 'Q3', 2020 UNION ALLWITH sale AS (Select 'Laptop' as Item, 'Miami' as City, 10 as No_of_Items, 'January' as Month UNION ALLSELECT 'Mobile', 'Houston', 25, 'March' UNION ALLSELECT 'Laptop', 'Miami', 8, 'March' UNION ALLSELECT 'TV', 'Austin', 7, 'February' UNION ALLSELECT 'Mobile', 'Austin', 18, 'January' UNION ALLSELECT 'Mobile', 'Miami', 22, 'June' UNION ALLSELECT 'TV', 'Houston', 9, 'May' UNION ALLSELECT 'Laptop', 'Austin', 11, 'April' UNION ALLSELECT 'Mobile', 'Miami', 15, 'May')SELECT * from sale;
Please feel free to save this as a view so you can use this table for the examples below. Otherwise, you need to use this ‘WITH’ clause in every query we will use later.
Here, we have Items as rows. Each row represents information about laptops, mobiles, or TV. The pivot function will help if you want these items as columns.
In the first exercise, we will aggregate the No_of_Items for each Item using ‘SUM’ and put the Items as columns:
SELECT * FROM sale pivot(sum(No_of_Items) for Item in ('Laptop', 'Mobile', 'TV'));
Here, our pivot columns are No_of_Items and Items. The other two columns (City and Month) are not mentioned in the pivot function. So, by default pivot function groups by using City and Month columns.
You can also use another select statement to specify the columns you want to pivot and the columns you want for grouping.
select * from (select Item, No_of_Items, Month from sale)pivot(sum(No_of_Items) for Item in ('Laptop', 'Mobile', 'TV'));
In this query, we used a select statement to choose three columns: Item, No_of_Items, and Month. No_of_Items and Items are used as the pivot columns in the pivot function and the Month column was left only for grouping and we used sum as the aggregate function.
We have three items in the Items column: Laptop, Mobile, and TV. In all the previous queries, all three of the Items have been converted to columns. If necessary you can exclude an Item as well. For example, you can use:
select * from (select Item, No_of_Items, Month from sale)pivot(sum(No_of_Items) for Item in ('Laptop', 'Mobile'));
More than one aggregate function can be used in the pivot function as well:
select * from (select No_of_Items, Item, City from sale)pivot(sum(No_of_Items) Total_num, AVG(No_of_Items) Avg_numfor Item in ('Laptop', 'Mobile'))
Here we used ‘sum’ and ‘average’ as aggregate functions. Notice we used an alias here. The pivot function asks for aliases when you want to use more than one aggregate function.
Let’s Unpivot
In the Big Query environment, there is an unpivot function as well that does exactly the opposite. Let’s make the dataset the other way now:
WITH sale AS (Select 'January' as Month, 31 as Laptop, 42 as TV, 75 as Mobile UNION ALLselect 'February', 35, 34, 61 UNION ALLselect 'March', 23, 23, 66 UNION ALLselect 'April', 29, 25, 55)select * from sale;
This time we are using the number of Laptops, TVs, and Mobiles as columns. Let’s make them as rows:
select * from saleunpivot(Sales_No for Items in (Laptop, TV, Mobile))
Notice, how it is rearranged. You can take it further. I will recreate the table with one more table:
WITH sale AS (Select 'January' as Month, 31 as Laptop, 42 as TV,75 as Mobile, 58 as Tablet UNION ALLselect 'February', 35, 34, 61, 73 UNION ALLselect 'March', 23, 23, 66, 63 UNION ALLselect 'April', 29, 25, 55, 45)select * from saleunpivot((Category1, Category2)for Seriesin ((Laptop, TV) as 'S1', (Tablet, Mobile) as 'S2'))
Now we have four Items: Laptop, Mobile, TV, and Tablet. I wanted to divide them into two categories. Category1 and Category2 become two columns. At the same time, We categorize them as Two Series as well that showing them as rows. I had to add one more Item because when we categorize them into two categories, we need the same number of columns in each category.
Conclusion
It is possible to achieve the same without using Pivot and Unpivot functions as well. But that will be a long way. These two functions can make your query shorter and more elegant when necessary.
More Reading:
A detailed tutorial
Pivot is a very simple function in BigQuery that can be very useful when you need to rotate rows into columns. It uses an aggregate function on rows and converts the categories in rows to columns. There is unpivot function that does totally an opposite operation. If you are a big query user in Google Cloud Platform and haven’t used Pivot and Unpivot functions yet, it is worth learning.
This article will focus on explaining Pivot and Unpivot functions with examples. For this We will create a dummy table as follows:
WITH Produce AS (SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter, 2020 as year UNION ALLSELECT 'Kale', 23, 'Q2', 2020 UNION ALLSELECT 'Kale', 45, 'Q3', 2020 UNION ALLWITH sale AS (Select 'Laptop' as Item, 'Miami' as City, 10 as No_of_Items, 'January' as Month UNION ALLSELECT 'Mobile', 'Houston', 25, 'March' UNION ALLSELECT 'Laptop', 'Miami', 8, 'March' UNION ALLSELECT 'TV', 'Austin', 7, 'February' UNION ALLSELECT 'Mobile', 'Austin', 18, 'January' UNION ALLSELECT 'Mobile', 'Miami', 22, 'June' UNION ALLSELECT 'TV', 'Houston', 9, 'May' UNION ALLSELECT 'Laptop', 'Austin', 11, 'April' UNION ALLSELECT 'Mobile', 'Miami', 15, 'May')SELECT * from sale;
Please feel free to save this as a view so you can use this table for the examples below. Otherwise, you need to use this ‘WITH’ clause in every query we will use later.
Here, we have Items as rows. Each row represents information about laptops, mobiles, or TV. The pivot function will help if you want these items as columns.
In the first exercise, we will aggregate the No_of_Items for each Item using ‘SUM’ and put the Items as columns:
SELECT * FROM sale pivot(sum(No_of_Items) for Item in ('Laptop', 'Mobile', 'TV'));
Here, our pivot columns are No_of_Items and Items. The other two columns (City and Month) are not mentioned in the pivot function. So, by default pivot function groups by using City and Month columns.
You can also use another select statement to specify the columns you want to pivot and the columns you want for grouping.
select * from (select Item, No_of_Items, Month from sale)pivot(sum(No_of_Items) for Item in ('Laptop', 'Mobile', 'TV'));
In this query, we used a select statement to choose three columns: Item, No_of_Items, and Month. No_of_Items and Items are used as the pivot columns in the pivot function and the Month column was left only for grouping and we used sum as the aggregate function.
We have three items in the Items column: Laptop, Mobile, and TV. In all the previous queries, all three of the Items have been converted to columns. If necessary you can exclude an Item as well. For example, you can use:
select * from (select Item, No_of_Items, Month from sale)pivot(sum(No_of_Items) for Item in ('Laptop', 'Mobile'));
More than one aggregate function can be used in the pivot function as well:
select * from (select No_of_Items, Item, City from sale)pivot(sum(No_of_Items) Total_num, AVG(No_of_Items) Avg_numfor Item in ('Laptop', 'Mobile'))
Here we used ‘sum’ and ‘average’ as aggregate functions. Notice we used an alias here. The pivot function asks for aliases when you want to use more than one aggregate function.
Let’s Unpivot
In the Big Query environment, there is an unpivot function as well that does exactly the opposite. Let’s make the dataset the other way now:
WITH sale AS (Select 'January' as Month, 31 as Laptop, 42 as TV, 75 as Mobile UNION ALLselect 'February', 35, 34, 61 UNION ALLselect 'March', 23, 23, 66 UNION ALLselect 'April', 29, 25, 55)select * from sale;
This time we are using the number of Laptops, TVs, and Mobiles as columns. Let’s make them as rows:
select * from saleunpivot(Sales_No for Items in (Laptop, TV, Mobile))
Notice, how it is rearranged. You can take it further. I will recreate the table with one more table:
WITH sale AS (Select 'January' as Month, 31 as Laptop, 42 as TV,75 as Mobile, 58 as Tablet UNION ALLselect 'February', 35, 34, 61, 73 UNION ALLselect 'March', 23, 23, 66, 63 UNION ALLselect 'April', 29, 25, 55, 45)select * from saleunpivot((Category1, Category2)for Seriesin ((Laptop, TV) as 'S1', (Tablet, Mobile) as 'S2'))
Now we have four Items: Laptop, Mobile, TV, and Tablet. I wanted to divide them into two categories. Category1 and Category2 become two columns. At the same time, We categorize them as Two Series as well that showing them as rows. I had to add one more Item because when we categorize them into two categories, we need the same number of columns in each category.
Conclusion
It is possible to achieve the same without using Pivot and Unpivot functions as well. But that will be a long way. These two functions can make your query shorter and more elegant when necessary.