SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 question on robvolks awesome crosstab query.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mdelgado
Posting Yak Master

USA
141 Posts

Posted - 05/24/2002 :  15:28:44  Show Profile  Reply with Quote
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.

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

thanks!

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 05/24/2002 :  16:51:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page

mdelgado
Posting Yak Master

USA
141 Posts

Posted - 05/24/2002 :  16:58:35  Show Profile  Reply with Quote

Awesome!

Thank you sir.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000