I am trying to make this post a foolproof guide that can help you to learn how to pivot multiple columns in Query.

If you are new to the Query in Google Sheets, there is nothing to worry. I have my tutorials already on the subject.

1. Learn Query Function with Examples in Google Sheets.

2. How to Use QUERY Function Similar to Pivot Table in Google Sheets.

Here is an illustration that can help you to understand what is single as well as multiple columns pivot in Query.

Nowadays Query is part of my Spreadsheet life. I am frequently testing different Query formula variations. As a result, you can see plenty of tutorials on this site related to Query.

Now let me come back to the topic – How to pivot multiple columns in Query in Google Sheets.

To learn this first you should know how to pivot a single column in Query. You can find all that info under the subtitle below.

Pivot Multiple Columns in Query – Example Formulas

Sample Data:

You can learn below how to use the pivot clause in Query and the purpose of it.

The Purpose of Pivot Clause in Query

I am normally using the pivot clause in Query to summarise my data by year/month/quarter wise. But when I want drill-down details, I use the Pivot Table menu option.

The other use of pivot clause is to format a long list of data suitable for data visualization (chart preparation).

How to Pivot a Single Column in Query in Google Sheets

The above is a dummy sales data. I know it has no resemblance to a real-life sales report. My aim is to make you understand how to pivot multiple columns in Query in Google Sheets.

So I think such basic data is easy to explain. Here are one example formula to single column pivot and the result.

Formula 1:

=query(A1:D7,"Select A, Sum(D) group by A Pivot B")

Result:

See how the above Query formula works.

The formula first 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 which acts as field labels.

In other words, the formula picks the unique salesperson names “Joy” and “Simon” from column A and sum their sales quantity (value in column D).

This value then distributed (moved to different columns) based on the product (column B).

The screenshot above speaks better than my explanation right?

Important Note:

In my formula, column B is in the pivot clause. So it may not appear in the select/group by clauses. My formula complies with this.

The column/columns listed in the select clause must be listed in the group by clause. Again my formula complies with this also.

If you check my sample data you can see that there is one more column, i.e. column C that doesn’t appear anywhere in the formula.

You can include this column in two ways in Query – either in the group by clause or in pivot clause.

How to Pivot Multiple Columns in Query in Google Sheets

Formula 1:

=query(A2:D7,"Select A,B, Sum(D) group by A,B pivot C")

In this formula 1, I’ve included the column C in the pivot clause. In the following formula 2, it’s in the group by clause together with the column B.

That means formula 2 contains multiple columns in the pivot clause in Google Sheets Query.

Formula 2:

=query(A2:D7,"Select A, Sum(D) group by A pivot B,C")

In multiple columns pivot, the unique values under the pivot clause columns are appeared as comma separated.

This way you can pivot multiple columns in Query in Google Sheets.

Additional Tips About Multiple Columns Pivot in Query

As per your requirement, you can use the columns in the pivot. I mean you can move the pivot column to group by or group by column to pivot.

If you use the pivot column in the group, it should appear in both the select clause as well as in the group by clause. I have already explained this above.

Additionally, as a shortcut, you can wrap your existing formula with the TRANSPOSE function to change the orientation.

Hope you have already the above sample data in your Google Sheets. If so, try the below formulas to see the similarity of 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. Enjoy!