7.6. 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 different.

A cursor can be compared to a recordset in ADO. The biggest difference is how you cycle through the records. In ADO, you use the MoveNext method to go through each record. When you write Stored Procedures, use the FETCH method, and instead of testing for EOF, test to make sure that the @@FETCH_STATUS equals 0. When the @@FETCH_STATUS does not equal 0, you have reached the end of the records, and you should exit your loop.

In the stored procedure listed below, I use While @@FETCH_STATUS = 0; you will find many examples on the Internet where people use @@FETCH_STATUS <> -1. Either method works, but I prefer to test for 0 because there is also a status of -2 that means that the record is missing. While this would be next to impossible in the example used here, it can come up in other cases. So, if I only want to work on successful fetches, I think testing for 0 is the way to go. To summarize, 0 means that the fetch was successful, -1 means that the fetch failed or it has reached the end of the records, and -2 means that the row fetched is missing.

Now, to actually build the stored procedure, go into the Northwind Database in SQL Server Enterprise Manager, right-click on Stored Procedures, and select New Stored Procedure. Enter the code in Example 7-6, and save the procedure.

Example 7-7. SQL Server simple crosstab stored procedure

 CREATE PROCEDURE [dbo].[SimpleXTab] @XField varChar(20), @XTable varChar(20), @XWhereString varChar(250), @XFunction varChar(10), @XFunctionField varChar(20), @XRow  varchar(40)  AS Declare @SqlStr nvarchar(4000) Declare @tempsql nvarchar(4000) Declare @SqlStrCur nvarchar(4000) Declare @col nvarchar(100) set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from             [' + @XTable + ']  ' + @XWhereString + ' Group By [' + @XField + ']' /* select @sqlstrcur */ exec sp_executesql @sqlstrcur  declare xcursor Cursor  for  Select * from ##temptbl_Cursor  open xcursor  Fetch next from  xcursor  into @Col While @@Fetch_Status = 0 Begin   set @Sqlstr = @Sqlstr + ", "   set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' +       @XField + " = '" +@Col + "' then [" + @XFunctionField +       "] Else 0 End) As [" + @XFunction + @Col + "]" ,'')   set @Sqlstr = @tempsql   Fetch next from xcursor into @Col End  /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */  set @tempsql = 'Select '  + @XRow + ', ' + @Sqlstr + ' From ' + @XTable +                              @XWhereString +  ' Group by ' + @XRow  set @Sqlstr = @tempsql  Close xcursor  Deallocate xcursor   set @tempsql = N'Drop Table ##temptbl_Cursor'   exec sp_executesql @tempsql  /*  Select @Sqlstr as [mk], len(@sqlstr) as [leng] */    exec sp_executesql @Sqlstr GO

I have created a view used to test the crosstab query stored procedure, but you can use this view in any SQL Server Database and on any table. It was written to take generic arguments and be reusable. The view can be created by right-clicking on the Views list item in Enterprise Manager on the Northwind database and selecting New View. You can also use a Create View statement and run it in Query Analyzer. If you use Query Analyzer, put the following code in for the view and run it. If you use the New View method from Enterprise Manager, skip the Create View statement by typing in everything starting at Select.

     CREATE VIEW dbo.vw_SampleQuery     AS     SELECT     dbo.Orders.ShipName, dbo.Categories.CategoryName,                dbo.Orders.ShipCountry,                (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity)                * (1 - dbo.[Order Details].Discount) AS OrderAmt     FROM       dbo.Orders INNER JOIN                dbo.[Order Details] ON dbo.Orders.OrderID =                dbo.[Order Details].OrderID INNER JOIN                dbo.Products ON dbo.[Order Details].ProductID =                dbo.Products.ProductID INNER JOIN                dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID

This creates a view called vw_SampleQuery that returns the columns I wanted for my crosstab query. To test the procedure, you can either create a pass-through query in Access or use Query Analyzer in SQL Server. To use the pass-through query in Access, create a pass-through query, using Northwind on SQL Server as the ODBC connection, and use the following code to run the procedure:

     Execute SimpleXTab 'CategoryName', 'vw_SampleQuery', '', 'Sum', 'OrderAmt', 'ShipCountry'

These arguments tell SQL Server that we want to have CategoryName from vw_SampleQuery as the columns. The empty string is the Where clause. In this case, I want all records. Type in Where and then the expression to restrict the number of records. It could be anything from a date range to a range of sale amounts, etc. The next argument asks what function you want to summarize by. Here we use Sum. Be careful if you use other functions because the stored procedure fills in 0 for the value when the match does not occur, so if you use min or max, these zeros could skew the results. If you want to use them, adjust the code. In any case, when you run the results from Access, you get the result that you see in Figure 7-4.

Figure 7-4. The result of the crosstab query example that summarizes OrderAmt by ShipCountry, run from Access as a pass-through query

I purposely did not go into great detail as to how this stored procedure works. If you read through it, you see that it is very similar to the created crosstab that we did in Chapter 5, except that this procedure uses just one column. I have also created a stored procedure that uses multiple columns in SQL Server, but it is beyond the scope of this book.

A few items that I want to cover are how the temporary table is declared and the limitation on the size of the query string. The temporary table is created with two number/pound signs (##) in front of the temporary table name; this creates a global temporary table. This table is subsequently dropped. The creation and the dropping of the table are enabled by a system-stored procedure that executes an SQL String. You will notice the N' in front of the SQL String, signifying that it is a Unicode string. This is important because a Unicode string is limited to 4,000 characters. A regular string is limited to 8,000 characters. The reason why a Unicode string can only have half the number of characters is that it uses 2 bytes per character, while a regular string uses 1 byte per string. This limitation will come up if you have a lot of columns and/or a long view name.

I have two lines of code in the stored procedure that are remarked out. To make a remark in a stored procedure, enter /* remark */. If you get an error when running the stored procedure, you can edit the procedure to put the remarks on the line that executes the SQL and takes the remarks off of the line that is getting the length of the string. If the string is too long, you either need to restrict your data with a where clause, use a shorter view name, or do something else to reduce the number of columns. I have created a stored procedure that gets around this limitation, but never used it in production because the necessary checking makes it run slowly.

There are also some third-party controls that you can purchase to produce crosstab queries on SQL Server, but the method I outline should be sufficient for most basic crosstab queries. It is also nice because you only need one stored procedure, and you can call it multiple times with different tables and other criteria. If you want more row headers than ShipCountry, you can put them in the same string and separate the field names by commas. (Do not place a comma at the end.)