4 ms·
SQL can make 2D data, but it extremely bad at it. It’s a good opportunity to wonder whether this part can be improved. “Pivot tables”: I often have a list of d
by eastbound 6mo ago
SQL can make 2D data, but it extremely bad at it. It’s a good opportunity to wonder whether this part can be improved.
“Pivot tables”: I often have a list of dates, then categories that I want to become columns. SQL can’t do that so there is a technique of spreading values to each column then doing a MAX of each value per date. It is clumsy and verbose but works perfectly… as long as categories are known in advance and fixed. There should be an SQL instruction to pivot those rows into columns.
Example: SELECT date, category, metric; -- I want to show 1 row per date only, with each category as a column.
```
SELECT date,
MAX(
CASE category WHEN ‘page_hits’ THEN metric END
) as “Page Hits”,
MAX(
CASE category WHEN ‘user_count’ THEN metric END
) as “User Count”
GROUP BY date;
^ Without MAX and GROUP BY:
2026-03-30 Value1 NULL
2026-03-30 NULL Value2
2026-03-31 Value1 NULL
(etc)
The MAX just merges all rows of the same date.
```
SQL should just have an instruction like: SELECT date, PIVOT(category, metric); to display as many columns as categories.
This thought should be extended for more than 2 dimensions.
- tn1 6mo agoDuckDB and Microsoft Access (!) have a PIVOT keyword (possibly others too). The latter is of course limited but the former is pretty robust - I've been able to use it for all I've needed.
- amichal 6mo agoPostgresSQL "crosstab ( source_sql text, category_sql text ) → setof record" https://www.postgresql.org/docs/current/tablefunc.html https://www.postgresql.org/docs/current/tablefunc.html VIA https://www.beekeeperstudio.io/blog/how-to-pivot-in-postgresql/ https://www.beekeeperstudio.io/blog/how-to-pivot-in-postgres... as a current googlable reference/guide
- andersmurphy 6mo ago> SQL can make 2D data, but it extremely bad at it. It’s a good opportunity to wonder whether this part can be improved. R*Trees are what you are looking for. The sqlite implementation supports up to 5 dimensions.
- h3lp 6mo agoin sqlite you can do it with FILTER: $ sqlite :memory: create table t (product,revenue, year); insert into t values ('a',10,2020),('b',14,2020),('c',24,2020),('a',20,2021),('b',24,2021),('c',34,2021); select product,sum(revenue) filter (where year=2020) as '2020',sum(revenue) filter (where year=2021) as '2021' from t group by product;