This answer to a beautiful Stack Overflow question I've given recently needs further explanation in a blog post. When working with Microsoft Excel, we can create beautiful and also very insightful Pivot Tables with grand totals. What are they? This is best explained visually. Assuming you have this normalised form for your raw data. As … Continue reading Creating a Microsoft Excel Style Pivot Table With Grand Totals in SQL
Tag: PIVOT
How to Calculate Multiple Aggregate Functions in a Single Query
At a customer site, I've recently encountered a report where a programmer needed to count quite a bit of stuff from a single table. The counts all differed in the way they used specific predicates. The report looked roughly like this (as always, I'm using the Sakila database for illustration): -- Total number of films … Continue reading How to Calculate Multiple Aggregate Functions in a Single Query
Impress Your Coworkers by Using SQL UNPIVOT!
I've recently encountered a very interesting question on Stack Overflow by an unnamed user. The question was about generating a table of the following form in Oracle, using a table valued function: Description COUNT ------------------- TEST1 10 TEST2 15 TEST3 25 TEST4 50 The logic that should be implemented for the COUNT column is the … Continue reading Impress Your Coworkers by Using SQL UNPIVOT!
How to use SQL PIVOT to Compare Two Tables in Your Database
This can happen ever so easily. You adapt a table by adding a new column: ALTER TABLE payments ADD code NUMBER(3); You go on, implementing your business logic - absolutely no problem. But then, later on (perhaps in production), some batch job fails because it makes some strong assumptions about data types. Namely, it assumes … Continue reading How to use SQL PIVOT to Compare Two Tables in Your Database
Are You Using SQL PIVOT Yet? You Should!
Every once in a while, we run into these rare SQL issues where we'd like to do something that seems out of the ordinary. One of these things is pivoting rows to columns. A recent question on Stack Overflow by Valiante asked for precisely this. Going from this table: +------+------------+----------------+-------------------+ | dnId | propNameId | … Continue reading Are You Using SQL PIVOT Yet? You Should!