In ETL, we often have to load many targets from a common set of base tables. Inevitably the targets are different enough that we have to create multiple queries or views to populate the many outputs of data. Which is fine, except now you’ve got yourself a maintenance nightmare, one that is avoidable. I’d like to share a trick with you to take a single query that can be recursively modified to dynamically change its structure to get different outputs. I call it, dynamic querying using block quotes.

The business case:

The sales department would like to take a single report that already exists and split it into two reports. The additional report will require different fitlers, aggregates, columns, and even joins!

Dynamic querying using block quotes, the full query:

The above query will output Quantity Sold and Value Sold for each Salesperson which has a salesperson flag. Then, by using this same query, we can programmatically, within a procedure or in our ETL, find and replace the block quotes to create an entirely different query of Quantities and Values by Package Type. You’ll need to find and replace all four of the following against your query text:

What’s fantastic about this approach, and hopefully you noticed, we can dynamically add joins, join conditions, filters, groupings, and columns to different block quote begin/end chunks.

This example was built using Microsoft’s World Wide Importers database. Oh, and it’s ANSI Standard.

Have you done anything like this, using block quotes, to enhance your reporting?