learning-it by example

Back to the video

Pivot, Unpivot and Lateral-Style Queries in SQLite

Six made-up sales pivot into 3 rows in SQLite, which has no PIVOT or LATERAL keyword.

Video #125 ยท SQL and analytics

Steps in the video

  1. 0:00 Six sales become three rows. SQLite has no PIVOT keyword, and still pivots.
  2. 0:24 The idea
  3. 0:45 Setup
  4. 1:01 The made-up sales
  5. 1:18 The runner
  6. 1:34 Pivot with conditional aggregation
  7. 2:26 Unpivot two ways
  8. 3:16 The lateral idea
  9. 3:43 Correlated subqueries
  10. 4:44 What other databases offer
  11. 5:10 What this does not show
  12. 5:28 Mistakes to avoid
  13. 5:54 My take and next steps
  14. 6:11 Recap

Files

  • queries.py (40 lines)
  • run.py (17 lines)
  • sales.py (22 lines)

Download the code (zip)

Tested with

Python 3.12.15

What this does not show

Six made-up sales in SQLite only; DuckDB, SQL Server and PostgreSQL features are described from their docs, not run, and no timings are measured.

Sources

Learning IT by Example.