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.Labels: Crosstab, Pivot, SQL Server |
