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!
Hi Prashanth,
I have the below table.
CUSTOMER NAME | DESCRIPTION | SALE QTY. | TOTAL PRICE | TEAM MEMB | PRODCUT HEAD
FRONT | SP1234 | 6 | 33000 | STAN | SCAN
IMS | AB1234 | 1 | 100000 | BARA | SOFT
FRONT | SP1234 | 1 | 46,422 | NASE | SCAN
ZEN | SP8888 | 1 | 46,422 | STAN | CONSUM
SOFTWARE | AB9876 | 1 | 565488 | NASE | SOFT
FRONT | SP8888 | 10 | 389545 | KINE | SOFT
ABC | XY1234 | 4 | 47,861 | BARA | SERV
IMS | ABA1234 | 1 | 246750 | BARA | SOFT
How to get the below output using Query pivot function?
TEAM MEMB | CUSTOMER NAME | DESCRIPTION | SALE QTY | SCAN | SOFT | CONSUM | SERV
STAN | FRONT | SP1234 | 7 | 79422
BARA | IMS | AB1234 | 2 | 346750
Rgds
Hari
Hi, Hari,
As far as I know, that’s not possible or easy to create with a Query Pivot Table. But I have a very clean formula based on SORTN and SUMIF array combination.
I’ll consider writing a tutorial based on your above input. In the meantime, please give me access to your sheet which I’ve already sent.
Note: In your sample, I think the team member corresponding to SP1234 is “STAN” in row # 2 and 4.
Hi, Hari,
From your sheet, I could realize that the offered SUMIF and SORTN combo is not practical. So to solve the problem, I have used two Query formulas in the nested form.
One Query uses the Pivot clause whereas the other doesn’t.
For other readers, the above data range is in A2:F10. For that range, I’ve used the following nested Query to summarize the data.
={query(A2:F10,"Select E,A,B, sum(D) group by E,A,B pivot F",1),query(A2:F10,"Select sum(C) group by E,A,B label sum(C) 'SALE QTY' ",1)}
Best,
GROUP BY and PIVOT is not working at the same time. It shows an error to me. How can I solve the problem?
Hi, Tham,
There is no such issue with Query! Are you using the Query clauses in the correct order?
Related: What is the Correct Clause Order in Google Sheets Query?
Without seeing the data and error, I won’t be able to comment further. You can consider sharing a screenshot showing error value or a sample of your sheet which won’t be published.