Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

 
  SQL Server Tips by Gama and Naughter

 

Crosstab SQL Server SP
 

SQL Server can emulate crosstab queries with Fixed Column Headings by means of one column for each heading containing an aggregate function with a CASE statement. The CASE statement is fundamental because it filters the right data for each column.

 

Example: To create a query with the total expenses per month and year using a pivot on the month column.

 

CREATE VIEW dbo.mycrosstab

AS

SELECT TOP 100 PERCENT YEAR(dbo.Orders.OrderDate) AS order_year,

      SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 1 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Jan,

               SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 2 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Feb,

      SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 3 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Mar,

               SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 4 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Apr,

      SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 5 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS May,

               SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 6 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Jun,

      SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 7 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Jul,

               SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 8 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Aug,

      SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 9 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Sep,

               SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 10 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Oct,

      SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 11 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Nov,

               SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 12 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Dec

FROM  dbo.Orders INNER JOIN

               dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

GROUP BY YEAR(dbo.Orders.OrderDate)

ORDER BY YEAR(dbo.Orders.OrderDate)

  

A correspondence between the two constructs, from Access and SQL Server, is shown in the following table:

 

TRANSFORM Sum([UnitPrice]*[quantity]*(1-[discount])) AS Total

SUM(CASE MONTH(dbo.Orders.OrderDate)

               WHEN 1 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS Jan,

SELECT Year([OrderDate]) AS order_year

SELECT TOP 100 PERCENT YEAR(dbo.Orders.OrderDate) AS order_year

FROM [Order Details] RIGHT JOIN Orders ON [Order Details].OrderID = Orders.OrderID

FROM  dbo.Orders INNER JOIN

               dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

GROUP BY Year([OrderDate])

GROUP BY YEAR(dbo.Orders.OrderDate)

PIVOT Month([OrderDate]) IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);

CASE MONTH(dbo.Orders.OrderDate)

               WHEN 1 THEN Ö

               ELSE 0 END


Table 27.2 Ė comparing Access SQL to TSQL

 

For most crosstab queries with Fixed Column Headings, the best solution is to manually change the query, the same way it is shown in this example. The alternatives are SPís, UDFís or XPís but the overhead for the first two is high and the maintenance for the latter is high; therefore, the simplest and most efficient solution is a query. SPís and XPís cannot directly be part of a SELECT statement, requiring temporary tables and adding more complexity.

 

Crosstab queries with variable number of columns and/or variable column names cannot be emulated in SQL Server with a query because of its structure. The two most common solutions are dynamic SQL, which is to put together the query code on the fly, and ADO, by building a recordset from calculated data.

 

Example: To create a query with the total expenses per month and year using a pivot on the year column.

 

This will require less code because there are only 3 years:

 

CREATE VIEW dbo.myCrosstab2

AS

SELECT TOP 100 PERCENT MONTH(dbo.Orders.OrderDate) AS order_month,

      SUM(CASE year(dbo.Orders.OrderDate)

                         WHEN 1996 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS [1996],

               SUM(CASE year(dbo.Orders.OrderDate)

               WHEN 1997 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS [1997],

      SUM(CASE year(dbo.Orders.OrderDate)

                         WHEN 1998 THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)

               ELSE 0 END) AS [1998]

FROM  dbo.Orders INNER JOIN

               dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

GROUP BY MONTH(dbo.Orders.OrderDate)

ORDER BY MONTH(dbo.Orders.OrderDate)

 

This query would be fine if dealing with historic data or any other kind of data that would be always limited to these 3 years. In most situations that will never happen and so the next example will handle the same problem but with a different technique: dynamic SQL.

 

Dynamic SQL is inherently slow because the code is created for each execution of the SP and then executed. The query plan for a SP with dynamic SQL will not be cached if the output from the SP changes constantly. The performance will be very dependant on the input data used by the query within the SP. This is also true for SPís with no dynamically generated code but the reality is that when dynamic SQL is the only solution, there will certainly be a performance loss. That is due to the complexity of the problem, this is the price to pay for flexibility.

 

CREATE PROCEDURE mycrosstabSP

AS

--returns a crosstab query from tables Orders and Order Details, with a variable number of columns

DECLARE @mYear int, @dynamicSQL varchar(8000), @crlf char(2)--declare temporary variables and carriage return+line feed

SET @crlf=char(13)+char(10)--carriage return+line feed

SET @dynamicSQL='SELECT TOP 100 PERCENT MONTH(dbo.Orders.OrderDate) AS order_month,'+@crlf--vertical header

DECLARE cur_year CURSOR--cursor to loop through the years

FOR SELECT DISTINCT YEAR(dbo.Orders.OrderDate)FROM dbo.Orders

OPEN cur_year--open cursor

FETCH NEXT FROM cur_year INTO @mYear--get year

WHILE @@FETCH_STATUS = 0--loop through all records

      BEGIN--add the column names and their aggregate data

      SET @dynamicSQL=@dynamicSQL+'SUM(CASE year(dbo.Orders.OrderDate)'+@crlf

      SET @dynamicSQL=@dynamicSQL+'WHEN '+CAST(@mYear as char(4))+' THEN (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount)'+@crlf

      SET @dynamicSQL=@dynamicSQL+'ELSE 0 END) AS ['+CAST(@mYear as char(4))+'],'+@crlf

      FETCH NEXT FROM cur_year INTO @mYear

      END

CLOSE cur_year--close cursor

DEALLOCATE cur_year--deallocate cursor

SET @dynamicSQL=LEFT(@dynamicSQL, LEN(@dynamicSQL)-3)--remove extra ,

--add JOIN, GROUP and ORDER

SET @dynamicSQL=@dynamicSQL+'FROM  dbo.Orders INNER JOIN

               dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID

GROUP BY MONTH(dbo.Orders.OrderDate)

ORDER BY MONTH(dbo.Orders.OrderDate)'

EXEC (@dynamicSQL)--execute dynamic SQL

GO

 

A cursor will work on each year to create columns that will depend on the rows from the Orders table. The only code that changes in the dynamic SQL query is the code for each column, which will be repeated an unknown amount of times and the values of the years which are also unknown. There are three blocks in the algorithm, one for the SELECT statement, another one for the aggregate functions in the columns and a third one for the JOIN/GROUP/ORDER.

 

Looking at the code the immediate question that rises is: can this code be modified to get the table input and work as a general crosstab query? Yes, with a few changes.

 

Example: Attempt to create a general crosstab query.

 

CREATE PROCEDURE myTransformSP @AggFunction varchar(10), @AggData varchar(100), @Select  varchar(100), @From  varchar(100),

@GROUPBY   varchar(100), @PIVOT  varchar(100)

AS

--returns a crosstab query from a general table, with a variable number of columns

DECLARE @header int, @dynamicSQL varchar(8000), @crlf char(2)--declare temporary variables and carriage return+line feed

CREATE TABLE #tmpTable (header int)

INSERT INTO #tmpTable

EXEC ('SELECT DISTINCT '+@PIVOT+' '+@From)

SET @crlf=char(13)+char(10)--carriage return+line feed

SET @dynamicSQL=@Select+','+@crlf--vertical header

DECLARE cur_header CURSOR--cursor to loop through the header values

FOR SELECT DISTINCT header from #tmpTable

OPEN cur_header--open cursor

FETCH NEXT FROM cur_header INTO @header--get header value

WHILE @@FETCH_STATUS = 0--loop through all records

      BEGIN--add the column names and their aggregate data

      SET @dynamicSQL=@dynamicSQL+@AggFunction+'(CASE '+@PIVOT+@crlf

      SET @dynamicSQL=@dynamicSQL+'WHEN '+CAST(@header as char)+' THEN ('+@AggData+')'+@crlf

      SET @dynamicSQL=@dynamicSQL+'ELSE 0 END) AS ['+CAST(@header as char)+'],'+@crlf

      FETCH NEXT FROM cur_header INTO @header

      END

CLOSE cur_header--close cursor

DEALLOCATE cur_header--deallocate cursor

SET @dynamicSQL=LEFT(@dynamicSQL, LEN(@dynamicSQL)-3)--remove extra ,

--add JOIN, GROUP and ORDER

SET @dynamicSQL=@dynamicSQL+@From+@crlf+@GROUPBY+@crlf+'ORDER BY '+REPLACE(@GROUPBY,'GROUP BY','')

EXEC (@dynamicSQL)--execute dynamic SQL

DROP TABLE #tmpTable

GO

 

Calling the SP is very much like using the TRANSFORM statement:

 

exec myTransformSP 'SUM'

, '[Order Details].UnitPrice * [Order Details].Quantity) * (1 - [Order Details].Discount'

, 'SELECT MONTH(Orders.OrderDate) AS order_month'

, 'FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID'

, 'GROUP BY MONTH(Orders.OrderDate)'

, 'year(Orders.OrderDate)'
 

exec myTransformSP 'SUM'

, '[Order Details].UnitPrice * [Order Details].Quantity) * (1 - [Order Details].Discount'

, 'SELECT YEAR(Orders.OrderDate) AS order_year'

, 'FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID'

, 'GROUP BY YEAR(Orders.OrderDate)'

, 'MONTH(Orders.OrderDate)' 

 

There are four details missing:

Fixed Column Headings.

Displaying the monthsí short names.

Using non numeric headers.

Sorting the result.

 

Using TRANSFORM in Access had simple ways to handle these details:

 

TRANSFORM Sum([UnitPrice]*[quantity]*(1-[discount])) AS Total

SELECT Year([OrderDate]) AS order_year

FROM [Order Details] RIGHT JOIN Orders ON [Order Details].OrderID = Orders.OrderID

GROUP BY Year([OrderDate])

ORDER BY Year([OrderDate]) DESC

PIVOT Format(([OrderDate]),"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

Sorting the result is easy, there will be one more parameter and the user will decide how to sort. Using names for the headers other than numbers is just a matter of changing the data type in the temporary table. Fixed Column Headings with user-defined aliases requires some string manipulation but its complexity is minimal. This is the new SP:

 

CREATE PROCEDURE myTransformFixedColSP @AggFunction varchar(10), @AggData varchar(100), @Select  varchar(100), @From  varchar(100),

@GROUPBY varchar(100), @ORDERBY  varchar(100), @PIVOT  varchar(100), @IN1 varchar(100)=NULL, @IN2 varchar(100)=NULL

AS

--returns a crosstab query from a general table, with a fixed number of columns

DECLARE @counter1 int, @counter2 int, @header1 varchar(100), @header2 varchar(100),

@dynamicSQL varchar(8000), @crlf char(2)--declare temporary variables and carriage return+line feed

DECLARE  @tmpTable TABLE(Header1 varchar(100), Header2 varchar(100))

IF @IN1 IS NOT NULL

      BEGIN

      IF @IN2 IS NULL --if there is no particular name for the columns, use the value

               SET @IN2=@IN1

      SET @IN1=@IN1+','--aliases are separated by ,

      SET @IN2=@IN2+','--aliases are separated by ,

      SET @counter1= CHARINDEX (',', @IN1)--update counter

      SET @counter2= CHARINDEX (',', @IN2)--update counter

      WHILE @counter1>0

               BEGIN

                         INSERT INTO @tmpTable

                         VALUES(LEFT(@IN1, @counter1-1), LEFT(@IN2, @counter2-1))

                         SET @IN1=RIGHT(@IN1, LEN(@IN1)-@counter1)--remove it from the input

                         SET @counter1= CHARINDEX (',', @IN1)--update counter

                         SET @IN2=RIGHT(@IN2, LEN(@IN2)-@counter2)--remove it from the input

                         SET @counter2= CHARINDEX (',', @IN2)--update counter

               END

      END

ELSE

      BEGIN

      CREATE TABLE #tmp (header varchar(100))

      INSERT INTO #tmp

      EXEC ('SELECT DISTINCT '+@PIVOT+' '+@From)

      INSERT @tmpTable

      SELECT header, header from #tmp

      DROP TABLE #tmp

      END

SET @crlf=char(13)+char(10)--carriage return+line feed

SET @dynamicSQL=@Select+','+@crlf--vertical header

DECLARE cur_header CURSOR--cursor to loop through the header values

FOR SELECT header1, header2 from @tmpTable

OPEN cur_header--open cursor

FETCH NEXT FROM cur_header INTO @header1, @header2--get header value

WHILE @@FETCH_STATUS = 0--loop through all records

      BEGIN--add the column names and their aggregate data

      SET @dynamicSQL=@dynamicSQL+@AggFunction+'(CASE '+@PIVOT+@crlf

      SET @dynamicSQL=@dynamicSQL+'WHEN '+@header1 +' THEN ('+@AggData+')'+@crlf

      SET @dynamicSQL=@dynamicSQL+'ELSE '+CASE WHEN @AggFunction='COUNT' THEN 'NULL' ELSE '0' END+' END) AS ['

      IF @IN1 IS NULL--variable columns with no aliases

               SET @dynamicSQL=@dynamicSQL+@header1+'],'+@crlf

      ELSE--Fixed Column Headings, aliases permitted

               SET @dynamicSQL=@dynamicSQL+@header2+'],'+@crlf--add it to the dynamic SQL string

      FETCH NEXT FROM cur_header INTO @header1, @header2

      END

CLOSE cur_header--close cursor

DEALLOCATE cur_header--deallocate cursor

SET @dynamicSQL=LEFT(@dynamicSQL, LEN(@dynamicSQL)-3)--remove extra ,

--add JOIN, GROUP and ORDER

SET @dynamicSQL=@dynamicSQL+@From+@crlf+@ORDERBY+@crlf+@GROUPBY+@crlf

--PRINT (@dynamicSQL)--execute dynamic SQL

EXEC (@dynamicSQL)--execute dynamic SQL

 

The last parameter is optional but, when used, it takes the comma-separated values and uses them as the column names. This is how to call the SP:

 

EXEC myTransformFixedColSP 'SUM'

, '[Order Details].UnitPrice * [Order Details].Quantity) * (1 - [Order Details].Discount'

, 'SELECT YEAR(Orders.OrderDate) AS order_year'

, 'FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID'

, 'ORDER BY YEAR(Orders.OrderDate)'

, 'GROUP BY YEAR(Orders.OrderDate)'

, 'MONTH(Orders.OrderDate)'

, '1,2,3,4,5,6,7,8,9,10,11,12'

, 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec'

  

The COUNT function had to be handled differently because the CASE ELSE had to return a NULL so that the record would not be counted. However, the other functions would get a zero so that it would not interfere with the calculations. This code takes care of that problem:

 

+CASE WHEN @AggFunction='COUNT' THEN 'NULL' ELSE '0' END+

 

Instead of assuming zero as the alternative to the column data in the CASE statement, it sets a NULL value for COUNT.

Example: Crosstab query with function COUNT.

 

This is the original Access code:

 

TRANSFORM Count([CompanyName])

SELECT [CompanyName]

FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

GROUP BY [CompanyName]

PIVOT Format([OrderDate], "yyyy");

 

Using the SP is very intuitive:

 

EXEC myTransformFixedColSP 'count'

, 'CompanyName'

, 'SELECT CompanyName'

, 'FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID'

, 'ORDER BY CompanyName'

, 'GROUP BY CompanyName'

, 'YEAR(OrderDate)'

 
Testing the SP with the Pubs database

 

Example: Calculate the sales for each title per year and per month.

 

The code:
 

EXEC myTransformFixedColSP 'SUM'

, 'titles.price*sales.qty'

, 'SELECT YEAR(sales.ord_date) AS order_year'

, 'FROM  dbo.sales INNER JOIN dbo.titles ON dbo.sales.title_id = dbo.titles.title_id'

, 'ORDER BY YEAR(sales.ord_date)'

, 'GROUP BY YEAR(sales.ord_date)'

, 'MONTH(sales.ord_date)'

, '1,2,3,4,5,6,7,8,9,10,11,12'

, 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec'

 

There are several months with no sales and it would be easier to read the data if those months were removed. It can be done by selecting which columns to show.

 

The code:

 

EXEC myTransformFixedColSP 'SUM'

, 'titles.price*sales.qty'

, 'SELECT YEAR(sales.ord_date) AS order_year'

, 'FROM  dbo.sales INNER JOIN dbo.titles ON dbo.sales.title_id = dbo.titles.title_id'

, 'ORDER BY YEAR(sales.ord_date)'

, 'GROUP BY YEAR(sales.ord_date)'

, 'MONTH(sales.ord_date)'

, '2,3,5,6,9,10,12'

, 'feb,mar,may,jun,sep,oct,dec'

 

This solution works if it is known in advance which columns to select, it would be easier if it would be done automatically. The solution is to use only the existing data that will determine the columns created by filtering the fixed column parameters.

 

This is the new UDF:

 

CREATE PROCEDURE myTransformVarColSP @AggFunction varchar(10), @AggData varchar(100), @Select  varchar(100), @From  varchar(100),

@GROUPBY varchar(100), @ORDERBY  varchar(100), @PIVOT  varchar(100), @IN1 varchar(100)=NULL, @IN2 varchar(100)=NULL

AS

--returns a crosstab query from a general table, with a variable number of columns

DECLARE @counter1 int, @counter2 int, @header1 varchar(100), @header2 varchar(100),

@dynamicSQL varchar(8000), @crlf char(2)--declare temporary variables and carriage return+line feed

DECLARE  @tmpTable TABLE(Header1 varchar(100), Header2 varchar(100))

IF @IN1 IS NOT NULL

      BEGIN

      IF @IN2 IS NULL --if there is no particular name for the columns, use the value

               SET @IN2=@IN1

      SET @IN1=@IN1+','--aliases are separated by ,

      SET @IN2=@IN2+','--aliases are separated by ,

      SET @counter1= CHARINDEX (',', @IN1)--update counter

      SET @counter2= CHARINDEX (',', @IN2)--update counter

      WHILE @counter1>0

               BEGIN

                         INSERT INTO @tmpTable

                         VALUES(LEFT(@IN1, @counter1-1), LEFT(@IN2, @counter2-1))

                         SET @IN1=RIGHT(@IN1, LEN(@IN1)-@counter1)--remove it from the input

                         SET @counter1= CHARINDEX (',', @IN1)--update counter

                         SET @IN2=RIGHT(@IN2, LEN(@IN2)-@counter2)--remove it from the input

                         SET @counter2= CHARINDEX (',', @IN2)--update counter

               END

      END

ELSE

      BEGIN

      CREATE TABLE #tmp (header varchar(100))

      INSERT INTO #tmp

      EXEC ('SELECT DISTINCT '+@PIVOT+' '+@From)

      INSERT @tmpTable

      SELECT header, header from #tmp

      DROP TABLE #tmp

      END

 

CREATE TABLE #tmp2 (header varchar(100))

INSERT INTO #tmp2

EXEC ('SELECT DISTINCT '+@PIVOT+' '+@From)

DELETE @tmpTable

WHERE header1 NOT IN (SELECT header from #tmp2)

DROP TABLE #tmp2

 

--select * from @tmpTable

SET @crlf=char(13)+char(10)--carriage return+line feed

SET @dynamicSQL=@Select+','+@crlf--vertical header

DECLARE cur_header CURSOR--cursor to loop through the header values

FOR SELECT header1, header2 from @tmpTable

OPEN cur_header--open cursor

FETCH NEXT FROM cur_header INTO @header1, @header2--get header value

WHILE @@FETCH_STATUS = 0--loop through all records

      BEGIN--add the column names and their aggregate data

      SET @dynamicSQL=@dynamicSQL+@AggFunction+'(CASE '+@PIVOT+@crlf

      SET @dynamicSQL=@dynamicSQL+'WHEN '+@header1 +' THEN ('+@AggData+')'+@crlf

      SET @dynamicSQL=@dynamicSQL+'ELSE '+CASE WHEN @AggFunction='COUNT' THEN 'NULL' ELSE '0' END+' END) AS ['

      IF @IN1 IS NULL--variable columns with no aliases

               SET @dynamicSQL=@dynamicSQL+@header1+'],'+@crlf

      ELSE--Fixed Column Headings, aliases permitted

               SET @dynamicSQL=@dynamicSQL+@header2+'],'+@crlf--add it to the dynamic SQL string

      FETCH NEXT FROM cur_header INTO @header1, @header2

      END

CLOSE cur_header--close cursor

DEALLOCATE cur_header--deallocate cursor

SET @dynamicSQL=LEFT(@dynamicSQL, LEN(@dynamicSQL)-3)--remove extra ,

--add JOIN, GROUP and ORDER

SET @dynamicSQL=@dynamicSQL+@From+@crlf+@ORDERBY+@crlf+@GROUPBY+@crlf

--PRINT (@dynamicSQL)--execute dynamic SQL

EXEC (@dynamicSQL)--execute dynamic SQL

GO

 

Testing the UDF:

 

EXEC myTransformVarColSP 'SUM'

, 'titles.price*sales.qty'

, 'SELECT YEAR(sales.ord_date) AS order_year'

, 'FROM  dbo.sales INNER JOIN dbo.titles ON dbo.sales.title_id = dbo.titles.title_id'

, 'ORDER BY YEAR(sales.ord_date)'

, 'GROUP BY YEAR(sales.ord_date)'

, 'MONTH(sales.ord_date)'

, '1,2,3,4,5,6,7,8,9,10,11,12'

, 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec'

 

Are these SPís the perfect solution for using crosstab queries in SQL Server?

There is no perfect solution, but for Fixed Column Headings it is better to use a custom SP for each query because it would contain no dynamic SQL and the execution plan would be cached by the optimizer. One interesting application of the crosstab SPís is that the dynamic SQL can be displayed in Query Analyzer with a PRINT statement and used in the custom crosstab SPís. This will save a lot of work! Unfortunately, sometimes Fixed Column Headings crosstab queries are not enough and the calculations might be very intensive.


The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter