Are you looking for a foolproof guide on how to pivot multiple columns in Query? Look no further! In this article, I’ll walk you through the steps and provide examples to help you master this feature in Google Sheets.
If you’re not familiar with Query in Google Sheets, don’t worry! I have already covered the basics in my previous tutorials. Here are a couple of articles you can refer to:
- Learn Query Function with Examples in Google Sheets
- How to Use QUERY Function Similar to Pivot Table in Google Sheets
To give you a better understanding of what single and multiple columns pivot in Query look like, take a look at the illustration below:
Query has become an essential part of my spreadsheet life, and I frequently experiment with different formula variations. As a result, I’ve compiled numerous tutorials related to Query on my website, Crawlan.com.
Now, let’s dive into the topic at hand – how to pivot multiple columns in Query in Google Sheets.
Pivot Multiple Columns in Query – Example Formulas
To get started, let’s take a look at some sample data:
In Query, the pivot clause is used to summarize data by year/month/quarter or format a long list of data suitable for data visualization. Here’s an example of how to use the pivot clause:
The Purpose of Pivot Clause in Query
The pivot clause is commonly used to summarize data by year/month/quarter. However, when I need more detailed information, I use the Pivot Table menu option. Another use of the pivot clause is to format data for data visualization, such as chart preparation.
How to Pivot a Single Column in Query in Google Sheets
Let’s walk through an example formula that demonstrates how to pivot a single column in Query. Consider the dummy sales data below:
Formula 1:
=query(A1:D7,"Select A, Sum(D) group by A Pivot B")Result:
The formula groups the values in column A and sums the values in column D accordingly. The pivot clause moves the summed values to different columns under the unique values in column B as field labels. In other words, the formula calculates the total sales quantity for each unique salesperson name (Joy and Simon) and distributes it based on the product.
Important Note:
In the above formula, column B is in the pivot clause and may not appear in the select/group by clauses. This formula is compliant with this requirement. The columns listed in the select clause must be listed in the group by clause as well, which is also satisfied by the formula.
You may have noticed that there is an additional column, column C, which doesn’t appear anywhere in the formula. You can include this column either in the group by clause or the pivot clause.
How to Pivot Multiple Columns in Query in Google Sheets
Here are two formulas that demonstrate how to pivot multiple columns in Query:
Formula 1:
=query(A2:D7,"Select A,B, Sum(D) group by A,B pivot C")Formula 2:
=query(A2:D7,"Select A, Sum(D) group by A pivot B,C")
In the multiple columns pivot, the unique values under the pivot clause columns are displayed as comma-separated. This is how you can pivot multiple columns in Query in Google Sheets.
Additional Tips About Multiple Columns Pivot in Query
Depending on your requirements, you can use different columns in the pivot. You can move the pivot column to the group by clause or vice versa. If you include the pivot column in the group by clause, it should also appear in the select clause. You can also use the TRANSPOSE function to change the orientation of your data.
If you have the sample data mentioned above in your Google Sheets, try the following formulas to see the outputs:
Formula 1:
=query(A1:D7,"Select B,C, Sum(D) group by B,C Pivot A")Formula 2:
=transpose(query(A2:D7,"Select A, Sum(D) group by A pivot B,C"))That’s all there is to it! Now go ahead and explore the power of pivoting multiple columns in Query in Google Sheets. Enjoy!
This article was inspired by Crawlan’s expertise in Google Sheets. To explore more helpful tutorials and guides, visit Crawlan.com.
