Hi all! This time I’ll try to dig a little bit into the fabulous realm of SQL and see how we can generate a nice report with dynamic columns.

The need: I had to generate a report showing some counts divided by month, basically the columns represent the months and the user has the possibility to pick a date range.

For example, imagine that you want to select the number of orders placed by all the customers by month.

Using the standard Northwind database as a reference, the first thing to do is to generate a list of months along with the relative start and end days:

The next step is “quite” easy: all we have to do is to generate a string containing the main query that picks all the customers and has many sub-queries,  one for each month, SELECT-ing the count of the orders.

As you may see, most of the work is done with lines 5-10 where we use the COALESCE function to concatenate the sub-queries created using the #dates temp table. Note that each query will contain a WHERE clause that filters the Order by Customer.

On lines 12-13 we create the final query to be executed, and finally, on line 16 we ask sp_executesql  to run our code.

Here’s a screenshot of the results:

Don’t forget to DROP the #dates table! 😀