Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Dynamic Crosstab - How it works.
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 09/28/2002 :  17:34:35  Show Profile  Reply with Quote
As Rob Volk said, I wanted to know how it works. In case someone else would like to know, here it is:

This example assumes the following statement taken from the first example using the pubs
database. The statement to execute crosstab is:

EXECUTE crosstab 'select title from titles inner join sales on (sales.title_id=titles.title_id) group by title',
'sum(qty)', 'stor_id', 'stores'

@pivot is the name of the pivot column from the table named in @table. From the statement above
it is stor_id. The following statement creates an empty table named ##pivot with one column named

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')

The next statement populates ##pivot with the unique values from the @pivot column (stor_id)
in the table named in @table (stores).

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

The result of the statement above puts the following values in the table ##pivot:


The following statement initializes the variable @sql to a single blank space. The parameter @sumfunc
was passed to crosstab as 'sum(qty)'. This statement has expanded it to 'sum(qty END)'.

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

The following statement selects the delimiter (@delim) to be used when to bound the column headings
from ##pivot. If the datatype is char or date a single quote (') is used. Otherwise a space is used. Since
stor_id, our pivot column is char(4) in the table stores, the delimiter is a single quote (').

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

The following statement creates the columns based on the values in ##pivot. The parameter @sumfunc
started out as 'sum(qty)' as passed to crosstab. It was expanded to 'sum(qty END)' two statements ago.
Now it will be expanded again and added to @sql for each value in ##pivot.

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

The phrase ['''' + convert(varchar(100), pivot) + ''' = ' ] takes the value from ##pivot and puts it in single
quotes and adds and equal sign. For the first entry in ##pivot that is ['6380' = ].

@sumfunc has the string 'sum(qty END)'. The phrase [stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0,
' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) ] expands
that to 'sum( CASE stor_id WHEN '6380' THEN qty END)'. A comma (,) is then added to the string.

For the first value from ##pivot we have the following phrase in @sql:
['6380' = sum( CASE stor_id WHEN '6380' THEN qty END), ].

A similar phrase is added for each value in ##pivot.

We are now finished with the temporary table ##pivot and it is dropped.

DROP TABLE ##pivot

The final comma(,) on @sql is dropped.

SELECT @sql=left(@sql, len(@sql)-1)

The value in the parameter @select was passed to crosstab. It is:

select title from titles inner join sales on (sales.title_id=titles.title_id) group by title

The following statement locates [ FROM ] in @select and inserts [, ], the contents of @sql, then another
blank before the word from. This completes the SQL string to be executed.

SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

The resulting string @select is:

select title , '6380' = sum( CASE stor_id WHEN '6380' THEN qty END), '7066' = sum( CASE stor_id WHEN '7066' THEN qty END), '7067' = sum( CASE stor_id WHEN '7067' THEN qty END), '7131' = sum( CASE stor_id WHEN '7131' THEN qty END), '7896' = sum( CASE stor_id WHEN '7896' THEN qty END), '8042' = sum( CASE stor_id WHEN '8042' THEN qty END) from titles inner join sales on (sales.title_id=titles.title_id) group by title

The statement EXEC (@select) completes the crosstab. Try it.

If anyone finds any errors in this interpretation please let me know. Many of the tricks I learned from here have been very useful.

I have created a modified version that allows the user to submit a destination table name into which the recordset will be stored. If anyone is interested I will be glad to submit it.

Great job Rob.

Starting Member

1 Posts

Posted - 04/12/2005 :  01:45:44  Show Profile  Reply with Quote

I was trying to use the crosstab procedure, when i observed that the crosstab doesn't return the value with decimal digits for Avg(Qty) aggregate function. Say if the average Qty was 2.37, it returns only 2 in the resultant recordset.
Was my observation correct, if so kindly give a solution for this.

Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 04/12/2005 :  08:26:47  Show Profile  Visit robvolk's Homepage  Reply with Quote
If the column being averaged is an integer data type, it will return an integer average. Any decimal portion is dropped. To get a decimal value:

SELECT AVG(Cast(myColumn AS money))

You can use decimal, numeric, real or float instead of money, but real and float may not give precise results.
Go to Top of Page

Starting Member

25 Posts

Posted - 07/19/2006 :  08:38:12  Show Profile  Reply with Quote
hi..i got a problem using your crosstab function..i appreciate it though, it works fine at first, but when my data can't accommodate the varchar(8000)limitation of the pivoted data..

please do reply asap if you've got a solution..i would highly appreciate if you have some suggestions..thanx:)
Go to Top of Page

Cybernetic Yak Master

11752 Posts

Posted - 07/19/2006 :  09:14:10  Show Profile  Visit spirit1's Homepage  Reply with Quote
this shuold point you in the right direction

and pivoting a query that's over 8k probably isn't a good idea to do on the server.
you might be better of doing it on the client:

Go with the flow & have fun! Else fight the flow
blog thingie:
Go to Top of Page

Starting Member

1 Posts

Posted - 09/17/2006 :  20:49:28  Show Profile  Reply with Quote
how if i wan to use this crosstab without aggregate function?
because my tables doesn't contain any numeric column.

-- yeanshi
Go to Top of Page

Most Valuable Yak

15732 Posts

Posted - 09/18/2006 :  22:40:23  Show Profile  Visit robvolk's Homepage  Reply with Quote
Min and Max work on non-numeric columns.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000