Dynamic Cross-Tab Procedure - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Cross-Tab Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-12 : 07:22:39

Inta writes "Hello

I am trying to run a procedure that would allow me to do cross-tabs using the following: The procedure compiles, however when I try to use it it does not work and i get the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

this is an example of the query i am submitting:

EXECUTE crosstab 'select brand, count(household) as household
from temp_table1
group by brand','count(household)','brand','temp_table1'

Anyone has an idea why this does not work?

Thank you very much for your help!!!

CREATE PROCEDURE crosstab
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

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

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

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'

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

DROP TABLE ##pivot

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

EXEC (@select)
SET ANSI_WARNINGS ON"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-12-12 : 07:23:52

99.9% of the time the cause of this error is too many pivoted values, which causes the dynamic SQL to exceed 8,000 characters. Jeff has an improved cross tab procedure here:

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources