Similar to traditional pivot tables, you can pivot multiple columns using the QUERY function in Google Sheets.

The Pivot Table feature (Insert > Pivot Table) supports multiple fields for grouping under Rows and Columns. Typically, we drag and drop fields into the Rows and Columns sections in the Pivot Table editor and add a field to aggregate under Values.

When using the QUERY function:

• The fields you drag and drop into Rows in a Pivot Table correspond to the fields in the GROUP BY clause.
• The fields you drag and drop into Columns in a Pivot Table correspond to the fields in the PIVOT clause.

Our topic, “Pivot multiple columns using QUERY,” focuses on using multiple fields in the PIVOT clause. Below, I’ve included three examples:

1. Single Field in the GROUP BY and PIVOT Clauses
2. Multiple Fields in the GROUP BY Clause and a Single Field in the PIVOT Clause
3. Single Field in the GROUP BY Clause and Multiple Fields in the PIVOT Clause
   (Multiple Columns in Pivot)

Pivot Multiple Columns Using QUERY and Data Requirements

You can pivot multiple columns using the QUERY function, but your data must meet specific requirements. What are they?

The data must have at least three columns: one for grouping (row grouping) and two for pivoting (column grouping). In this case, you can most likely use the COUNT function for aggregation. If your data includes a fourth column with numeric values, you can use it for additional aggregation operations.

For this tutorial, we’ll use the following sales data. The dataset is well-structured to demonstrate how to pivot multiple columns using the QUERY function.

1. Single Field in the GROUP BY and PIVOT Clauses

Assume you want to create a Pivot Table to show the total sales of products by each salesperson. You can use the following QUERY formula:

=QUERY(A1:D, "SELECT A, SUM(D) WHERE A IS NOT NULL GROUP BY A PIVOT B", 1)

Result:

Here’s how it works:

• Column A (Salesperson) is used in the GROUP BY clause.
• Column B (Product) is used in the PIVOT clause.
• It’s important to note that any field used in the GROUP BY clause must also be specified in the SELECT clause.

If you were to create this using the built-in Pivot Table feature (Insert > Pivot Table):

• Add Salesperson to Rows.
• Add Product to Columns.

I hope this basic example helps you understand how to pivot multiple columns using the QUERY function later in this tutorial.

2. Multiple Fields in the GROUP BY Clause and a Single Field in the PIVOT Clause

This approach involves using multiple fields in the GROUP BY clause and a single field in the PIVOT clause.

For example, we can group our sample data by Salesperson and Product, and pivot the Zone column. This will provide a summary of each salesperson’s product sales in different regions.

=QUERY(A1:D, "SELECT A, B, SUM(D) WHERE A IS NOT NULL GROUP BY A, B PIVOT C", 1)

Result:

In this formula:

• Columns A (Salesperson) and B (Product) are used in both the SELECT and GROUP BY clauses.
• Column C (Zone) is used in the PIVOT clause.

If you were to create this using a regular Pivot Table:

• Add Salesperson and Product to Rows.
• Add Zone to Columns.

However, note that the example above is not pivoting multiple columns using the QUERY function. You’ll find that much-awaited example in the section below.

3. Single Field in the GROUP BY Clause and Multiple Fields in the PIVOT Clause (Pivot Multiple Columns Using QUERY)

The following formula groups the Salesperson and pivots the Product and Zone columns:

=QUERY(A1:D, "SELECT A, SUM(D) WHERE A IS NOT NULL GROUP BY A PIVOT B, C", 1)

Result:

If you were to create this using a regular Pivot Table:

• Add Salesperson to Rows.
• Add Product and Zone to Columns.

When you pivot multiple columns using QUERY or a regular Pivot Table, the result will be wider data compared to the taller data produced by using multiple fields in the GROUP BY clause (multiple columns in Rows in a traditional Pivot Table).

If you compare the output of the last two formulas, you’ll notice that the results are the same, but the arrangement of the data is different.

Ultimately, it’s up to you whether you want to pivot multiple columns or group by multiple columns in QUERY.

Resources

• Using QUERY Function Similar to Pivot Table in Google Sheets
• How to Format the QUERY Pivot Header Row in Google Sheets
• Replace Blank Cells with 0 in Query Pivot in Google Sheets
• How to Retain All Column Labels in Query Pivot in Google Sheets
• Sort QUERY Pivot Headers in Descending Order in Google Sheets
• A Simple Formula to Unpivot a Dataset in Google Sheets
• Unpivot Excel Data Fast: Power Query & Dynamic Array Formula