← Back

Create a Pivot Table in SQL the Easy Way

2024-04-29 // Mark Tressler

While there is a PIVOT() syntax you can use in SQL to pivot your data from rows to columns, Row Zero offers a much easier way to create actual pivot tables in a spreadsheet from any data source in a few easy steps.

SQL to Pivot Table in 5 Easy Steps:

  1. Open up a workbook in Row Zero:
    Row Zero is the world’s fastest spreadsheet and can handle massive data sets. Login or sign up for free to get started.

  2. Connect to your data source (Postgres, Snowflake, Databricks, etc) connect data

  3. Select * from whatever table(s) you want and click Run connect data

  4. A Connected Table is generated in Row Zero that is a live connection to your data source connect data

  5. Click create Pivot Table connect data

Result: A live pivot table directly connected to your data source

Now you have an easily configurable pivot table with direct connection to your data source. No more SQL to pivot, filter, calculate, or configure your data. Do all your favorite pivot table moves and spreadsheet formulas right here. pivot table result

Bonus Points: Post your pivot table back to your data warehouse

You can export your pivot table back to your data warehouse as a new data table. By default these are named starting with “rz_” in order to prevent overwriting any original data.

So there you have it: SQL pivot table in a spreadsheet with a few easy steps. Pivot multiple columns and create dynamic pivot tables in a few clicks.

How does this work?

Row Zero is a supercharged spreadsheet that allows you to connect directly to any data source. It’s blazing fast and can handle billions of rows. Given this horsepower, you can use a basic SQL query like “select *” from most data tables and pull it right into a Row Zero spreadsheet. From there you can do all your normal spreadsheet work and create all the pivot tables you can dream up. Ready to try it out?

Try Pivot Tables in Row Zero

How to use the Pivot function in SQL Server

Looking to stay within SQL Server and not go to a spreadsheet? Pivoting in SQL Server involves transforming data from rows to columns using the PIVOT operator. Here’s an example:

Sample Data
Let’s use an example table called Sales with the following data: sample data

Pivot Query
Let’s pivot this data so each year becomes a column and each row shows the amount for each sales rep using the following SQL query:

SELECT SalesRep, [2023] AS FY2023, [2024] AS FY2024
FROM
(
    SELECT SalesRep, Year, Amount
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(Amount)
    FOR Year IN ([2023], [2024])
) AS PivotTable;


Example Result
The result of the pivot query would be: sample data

How it works

  1. Subquery (AS SourceTable): The subquery selects the relevant columns (SalesRep, Year, and Amount) from the Sales table.
  2. PIVOT Operator: The PIVOT operator gets applied to the subquery and sums the Amount for each Year, creating a column for each year specified.
  3. SELECT Statement: The outer SELECT statement pulls the SalesRep and the pivoted columns, renaming them as needed.

While this isn’t too complicated, we’re also dealing with a super simple example. To get a dynamic pivot table in SQL from a big data table with multiple columns, it’s much easier to just use Row Zero to pull the data into a spreadsheet and pivot dynamically as many times as you want. Try it out for yourself!

Try Pivot Tables in Row Zero