Integrating Excel and Access

Crosstab Queries on SQL Server

If you have been using Access, you may be quite familiar with using Crosstab queries. However, you may be surprised to find out that SQL Server does not support this type of query. I'm not sure why not, but I had a client who wanted to do a crosstab out of SQL Server, and I had to find a way to do it. I looked at bringing the table into Access and then just running the crosstab from Access. That would have been a viable method, but we were calling the query from a VB application that was not using an Access database at all. What resulted was a generic Stored Procedure that returned a crosstab query. However, there were some stumbling blocks along the way that deserve careful attention.

I looked in Books Online for SQL Server (the help file that comes with SQL Server) and found that you could use the Case...When statement to simulate a crosstab query. There is an example of how to do it by pivoting quarterly sales data in Books Online. However, this is very limited, and in that case, you know the exact number of columns that you need. The bigger question was: how can I create a list of the columns that I need dynamically from arguments passed to a stored procedure? The solution was to create a temporary table by using the sp_executesql system-stored procedure. Doing this enabled me to not use dynamic text to open the list of columns. I was able to declare the cursor with a line of text that did not change even if the underlying temporary table was very ...