[nojito]

> There's a reason why, despite the promises of every BI tool that it will "simplify" your database and "empower users", none of them have toppled SQL or even added anything useful that SQL could incorporate.

Pivoting data is still extremely painful using raw SQL

[listenallyall]

That's a semi-valid objection but I'd argue that's not really within the scope of SQL (and the PIVOT keyword is not an official part of the language, I believe). SQL is a language to interact with databases, SELECT queries are specifically for extracting data out of a database. Pivoting is typically something you do with data you've already extracted, that currently resides in memory, which is why it is fast, there's no additional data retrieval each time you change the aggregations or filters in a pivot table. Put it this way -- a SQL-based pivot table engine, that re-queries the database with every change, would perform awfully compared to a simple Excel PivotTable.

Similarly, you can't use SQL to apply color formatting to any of the result rows or values -- but that was never a goal SQL intended to achieve.

[CRConrad]

> and the PIVOT keyword is not an official part of the language

Yeah, that's probably a large part of the reason why pivoting data is such a pain in the backside.

[BenoitP]

> PIVOT

The postgres dialect has it IIRC.