How to Use the SQL Pivot Clause in Google Sheets
Learn how to use Google Sheets SQL’s Pivot Clause clause to summarize large amounts of data by using two categories, one defining the rows and another defining the columns.
The pivot clause is a clause that allows you to transpose the distinct values in a column into new columns, without using the group by clause. In this tutorial, we will try to use the pivot clause by itself and combine it with the group by clause to get a more comprehensive summary of the data from our source sheet.
How to apply pivot clause
For our example, we have the total profit from each product per week. The original query for it is (without quotes) :
"select B, sum(H) group by B"
This query will group all the entries by what is listed in column B (the item name) and then get the sum of what is listed in column H, listing the sums in the next column. The result will look like the one in the following image:
Let's say we wish to flip this original array, where this time we set each column for an item. To give each item a column, we will rewrite the query as follows (without quotes):
"select sum(H) pivot B"
Thus our formula would be:
=query(week32!A2:H634, "select sum(H) pivot B")
And the result will look like this:
The aggregation function was applied to all the entries within the same column. By using the pivot clause, the entries are automatically sorted by the individual items in the specified column.
How to combine pivot clause with group by clause
The pivot clause can be combined with group by clause to make more sophisticated tables. To improve our example above, we wish to divide the total profit by day, one day a row. To do so, we add the following before the pivot clause (without quotes):
"group by A"
The query will look like this (without quotes):
"select A, sum(H) group by A pivot B"
We will plug this inside the QUERY function:
=query(week32!A2:H634, "select A, sum(H) group by A pivot B")
And the results will look like this:
It is important to add the column of date after the select clause for Google Sheets to list them on the first column.
A sample worksheet
To help you more, we have included this sample worksheet for you to see how it works: SQL Pivot Query Sample Workbook.
A bit of a hassle?
This is a fairly straightforward tutorial (unlike some of our other SQL ones), but you most likely dream of an app where you can do the same task in a few clicks to make your decision making easier.
If that’s what you fancy, consider trying Lido. Within seconds, you can now access all the relevant metrics without going through the hassle of accessing the SQL databases of your eCommerce platforms and then coding the formulas to process them. Let our platform do it all for you!
