Dynamic Crosstab - How it works.

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

All Forums

Site Related Forums

Article Discussion

Dynamic Crosstab - How it works.

Author

Topic

dhealy
Starting Member

1 Post

Posted - 2002-09-28 : 17:34:35

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
pivot.

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:

6380
7066
7067
7131
7896
8042

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.

COMMENTS:
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.

jeetendra
Starting Member

1 Post

Posted - 2005-04-12 : 01:45:44

Hi,

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.

Thanks
Jeet

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-12 : 08:26:47

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.

kristine
Starting Member

25 Posts

Posted - 2006-07-19 : 08:38:12

hi..i got a problem using your crosstab function..i appreciate it though, it works fine at first, but when my data increased..it 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:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-07-19 : 09:14:10

this shuold point you in the right direction
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

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:
http://weblogs.sqlteam.com/jeffs/archive/2005/05/12/5127.aspx

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

yeanshi
Starting Member

1 Post

Posted - 2006-09-17 : 20:49:28

how if i wan to use this crosstab without aggregate function?
because my tables doesn't contain any numeric column.

-- yeanshi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-18 : 22:40:23

Min and Max work on non-numeric columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources