SQL Hacks

Chapter 10. Reporting

You might run your quarterly reports only a few times a year, so the speed of your SQL queries may not be your prime concern. Instead, it might be more important that the parameters of the queries be easy to change. If you are going to look at these queries only infrequently, it is even more important that the queries are readable.

Often the SQL statement is not the end of the process. The results of your SQL queries may be imported into another package, such as a spreadsheet or a word processor, so that extra formatting can be applied to the data before the report is printed or distributed electronically. Even though these other packages may have facilities for further processing, you might want to do as much processing as possible in SQL.

This chapter contains hacks on parameterizing queries, and processing data so that it’s ready for export.

Fill in Missing Values in a Pivot Table

Microsoft Excel can generate pivot tables. You can’t produce a pivot table using SQL alone, but you can use SQL to help Excel do a better job by supplying missing values.

A pivot table displays counts, sums, or averages arranged in a grid. You can’t produce a pivot table automatically in SQL because the column headings, and indeed the number of columns, depend on the data in the table. You can build a table with column headings showing aggregates of different values [Hack #51], but you cannot have the columns generated automatically in SQL.

Creating a pivot table in Excel is not difficult, ...