🧠 Second Brain

Search

Search IconIcon to open search

Pivot Table

Last updated Feb 5, 2025

Are pivot tables making a comeback in 2024? Rill Developer incorporates pivot tables within its web UI, Cube enables pivot table functionality in Excel WITH Cube, and DuckDB has introduced its own implementation. Tweet

# What is a Pivot Table?

A pivot table is a table of values that are aggregations of groups of individual values from a more extensive table (such as from a database, spreadsheet, or business intelligence program) within one or more discrete categories.

The aggregations or summaries of the groups of the individual terms might include sums, averages, counts, or other statistics. A pivot table is the outcome of the statistical processing of tabularized raw data and can be used for decision-making.

Although pivot table is a generic term, Microsoft held a trademark on the term in the United States from 1994 to 2020.

Pito Salas is a Curaçaoan-American Cambridge, Massachusetts-based software developer. While working with Lotus’ Advanced Technology Group in 1986, Salas invented the pivot table, a “next-generation” spreadsheet concept that was released by Lotus in 1989, as Lotus Improv. Source - Wiki

# History

gantt
    title Milestones in Spreadsheet & Pivot Table Software
    dateFormat YYYY
    axisFormat %Y
    
    section Early Foundations
    Paper Spreadsheets         :milestone, m1, 1960, 0d
    
    section Digital Revolution & Spreadsheets
    IBM Financial Planning     :milestone, m2, 1970, 0d
    VisiCalc           :milestone, m3, 1979, 0d
    Lotus 1-2-3        :milestone, m4, 1983, 0d
    Excel for Mac       :milestone, m5, 1985, 0d
    
    section Desktop Era
    Lotus Improv (First Pivot):milestone, m7, 1989, 0d
    Excel 5.0 (Pivot Tables)  :milestone, m8, 1993, 0d
    QlikView       :milestone, m9, 1993, 0d
    Tableau      :milestone, m10, 2003, 0d
    PowerPivot       :milestone, m12, 2010, 0d
    
    section Cloud Era
    Power BI   :milestone, m13, 2015, 0d
    Sigma     :milestone, m14, 2019, 0d
    Cube, Omni, Rill     :milestone, m14, 2025, 0d

    click m8 href "Pivot Tables introduced"
    click m7 href "First pivot concept"
    click m12 href "PowerPivot & Power Query"
    click m13 href "Python integration"
    click m14 href "Python integration"

From Rill | Why Pivot Tables Never Die.

# Examples of the Resurgence

# DuckDB

DuckDB introduces pivot tables through the Excel pivoting API. More details can be found in Creating a SQL-Only Extension for Excel-Style Pivoting in DuckDB – DuckDB:

The pivot_table function allows for Excel-style pivots, including subtotals, grand_totals, and more. It is also very similar to the Pandas pivot_table function, but with all the scalability and speed benefits of DuckDB. It contains over 250 tests, so it is intended to be useful beyond just an example!

To use the pivot table functionality in DuckDB:

1
2
INSTALL pivot_table FROM community;
LOAD pivot_table;

2024-09-27

An example by Michael Simons on pv/bin/import_and_update.sh at main · michael-simons/pv · GitHub.

# Rill

2024-09-10:

Vimeo Video: Introducing the Rill pivot table

For more information, see Rill Developer and Rill | Introducing the Rill pivot table.

# Cube

Cube integrates with Google Sheets and Excel. The following example shows integration with Google Sheets as of 2024-09-26:

Source: How Cube Improves Spreadsheet Analytics - Cube Blog

Artyom Keydunov on LinkedIn: Excited to announce the release of Google Sheets integration for Cube! 🎉…

# Pandas

Pivot tables are so not bad that even pandas has a pivot_table function… Bsky

Pandas Docs on Pivot Table

# History

# Further Readings


Origin: Excel for ever, DuckDB Con in Seattle
References:
Created 2024-09-24