Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page