question on robvolks awesome crosstab query.

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

Transact-SQL (2000)

question on robvolks awesome crosstab query.

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-05-24 : 15:28:44

I've been staring at his code for about 45 minutes now and I can't figure out how to modify it so that it creates a table from the final crosstab.

Rather than run a select statement and display the results, I need to have the code create a table named robvolkcrosstab.

any help would be much appreciated.

[url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=6216[/url]

thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-24 : 16:51:32

quote:
I've been staring at his code for about 45 minutes now

As Mike Femenella will tell you, prolonged exposure to my code is REALLY REALLY BAD. You might want to check out a good therapist afterwards.

I remember someone asking this before, and I thought it was posted in the comments, but here it is:

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 + ' INTO tableName ')

EXEC (@select)
SET ANSI_WARNINGS ON

The part highlighted in red does the trick, just change the table name to whatever you want.

mdelgado
Posting Yak Master

141 Posts

Posted - 2002-05-24 : 16:58:35

Awesome!

Thank you sir.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources