How to Pivot Multiple Columns in Query in Google Sheets

Published on

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.

understand what is single as well as multiple columns pivot

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:

sample data for query pivot - 2 columns

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:

example to single column pivot in Query

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")

single column pivot and multiple columns pivot

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!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

5 COMMENTS

  1. 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,

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.