An in depth tutorial
Pivot is a quite simple operate in BigQuery that may be very helpful when it is advisable to rotate rows into columns. It makes use of an mixture operate on rows and converts the classes in rows to columns. There’s unpivot operate that does completely an reverse operation. In case you are a giant question person in Google Cloud Platform and haven’t used Pivot and Unpivot features but, it’s value studying.
This text will deal with explaining Pivot and Unpivot features with examples. For this We are going to create a dummy desk as follows:
WITH Produce AS (SELECT 'Kale' as product, 51 as gross sales, 'Q1' as quarter, 2020 as yr UNION ALLSELECT 'Kale', 23, 'Q2', 2020 UNION ALLSELECT 'Kale', 45, 'Q3', 2020 UNION ALLWITH sale AS (Choose 'Laptop computer' as Merchandise, 'Miami' as Metropolis, 10 as No_of_Items, 'January' as Month UNION ALLSELECT 'Cell', 'Houston', 25, 'March' UNION ALLSELECT 'Laptop computer', 'Miami', 8, 'March' UNION ALLSELECT 'TV', 'Austin', 7, 'February' UNION ALLSELECT 'Cell', 'Austin', 18, 'January' UNION ALLSELECT 'Cell', 'Miami', 22, 'June' UNION ALLSELECT 'TV', 'Houston', 9, 'Might' UNION ALLSELECT 'Laptop computer', 'Austin', 11, 'April' UNION ALLSELECT 'Cell', 'Miami', 15, 'Might')SELECT * from sale;
Please be at liberty to avoid wasting this as a view so you should utilize this desk for the examples under. In any other case, it is advisable to use this ‘WITH’ clause in each question we are going to use later.
Right here, we have now Objects as rows. Every row represents details about laptops, mobiles, or TV. The pivot operate will assist if you would like these things as columns.
Within the first train, we are going to mixture the No_of_Items for every Merchandise utilizing ‘SUM’ and put the Objects as columns:
SELECT * FROM sale pivot(sum(No_of_Items) for Merchandise in ('Laptop computer', 'Cell', 'TV'));
Right here, our pivot columns are No_of_Items and Objects. The opposite two columns (Metropolis and Month) aren’t talked about within the pivot operate. So, by default pivot operate teams by utilizing Metropolis and Month columns.
You may also use one other choose assertion to specify the columns you wish to pivot and the columns you need for grouping.
choose * from (choose Merchandise, No_of_Items, Month from sale)pivot(sum(No_of_Items) for Merchandise in ('Laptop computer', 'Cell', 'TV'));
On this question, we used a choose assertion to decide on three columns: Merchandise, No_of_Items, and Month. No_of_Items and Objects are used because the pivot columns within the pivot operate and the Month column was left just for grouping and we used sum as the combination operate.
Now we have three objects within the Objects column: Laptop computer, Cell, and TV. In all of the earlier queries, all three of the Objects have been transformed to columns. If essential you’ll be able to exclude an Merchandise as properly. For instance, you should utilize:
choose * from (choose Merchandise, No_of_Items, Month from sale)pivot(sum(No_of_Items) for Merchandise in ('Laptop computer', 'Cell'));
Multiple mixture operate can be utilized within the pivot operate as properly:
choose * from (choose No_of_Items, Merchandise, Metropolis from sale)pivot(sum(No_of_Items) Total_num, AVG(No_of_Items) Avg_numfor Merchandise in ('Laptop computer', 'Cell'))
Right here we used ‘sum’ and ‘common’ as mixture features. Discover we used an alias right here. The pivot operate asks for aliases whenever you wish to use multiple mixture operate.
Let’s Unpivot
Within the Massive Question atmosphere, there’s an unpivot operate as properly that does precisely the other. Let’s make the dataset the opposite manner now:
WITH sale AS (Choose 'January' as Month, 31 as Laptop computer, 42 as TV, 75 as Cell UNION ALLchoose 'February', 35, 34, 61 UNION ALLchoose 'March', 23, 23, 66 UNION ALLchoose 'April', 29, 25, 55)choose * from sale;
This time we’re utilizing the variety of Laptops, TVs, and Mobiles as columns. Let’s make them as rows:
choose * from saleunpivot(Sales_No for Objects in (Laptop computer, TV, Cell))
Discover, how it’s rearranged. You’ll be able to take it additional. I’ll recreate the desk with yet another desk:
WITH sale AS (Choose 'January' as Month, 31 as Laptop computer, 42 as TV,75 as Cell, 58 as Pill UNION ALLchoose 'February', 35, 34, 61, 73 UNION ALLchoose 'March', 23, 23, 66, 63 UNION ALLchoose 'April', 29, 25, 55, 45)choose * from saleunpivot((Category1, Category2)for Collectionin ((Laptop computer, TV) as 'S1', (Pill, Cell) as 'S2'))
Now we have now 4 Objects: Laptop computer, Cell, TV, and Pill. I wished to divide them into two classes. Category1 and Category2 develop into two columns. On the identical time, We categorize them as Two Collection as properly that exhibiting them as rows. I had so as to add yet another Merchandise as a result of once we categorize them into two classes, we want the identical variety of columns in every class.
Conclusion
It’s doable to realize the identical with out utilizing Pivot and Unpivot features as properly. However that will probably be a good distance. These two features could make your question shorter and extra elegant when essential.
