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
- 0:00 Six sales become three rows. SQLite has no PIVOT keyword, and still pivots.
- 0:24 The idea
- 0:45 Setup
- 1:01 The made-up sales
- 1:18 The runner
- 1:34 Pivot with conditional aggregation
- 2:26 Unpivot two ways
- 3:16 The lateral idea
- 3:43 Correlated subqueries
- 4:44 What other databases offer
- 5:10 What this does not show
- 5:28 Mistakes to avoid
- 5:54 My take and next steps
- 6:11 Recap
Files
- queries.py (40 lines)
- run.py (17 lines)
- sales.py (22 lines)
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
- https://docs.python.org/3.12/library/sqlite3.html
- https://duckdb.org/docs/2.0/sql/statements/pivot.html
- https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot
- https://www.postgresql.org/docs/current/queries-table-expressions.html
- https://www.postgresql.org/docs/current/tablefunc.html
- https://www.sqlite.org/lang_aggfunc.html
- https://www.sqlite.org/lang_expr.html
- https://www.sqlite.org/lang_keywords.html
Learning IT by Example.