MySQL Pivot Table Query
· 6 min read
One of the most important visualization techniques is Pivot Tables. They provide side-by-side comparison of data along both rows and columns. And often the columns end up being the time dimension (year, quarter or month). The power of pivot tables is amplified if there are sub-totals and grand-totals along with just the totals. But the question is whether all this can be done with one single SQL query or at least as fewer queries as possible? If a database supports the CUBE group by clause, such as PostgreSQL and Oracle, then this is simple and can be done in a single query. But what about MySQL the other popular database that only supports ROLLUP group by clause?