|
robvolk
Most Valuable Yak
USA
15557 Posts |
 Posted - 05/24/2002 : 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.
|
 |
|
question on robvolks awesome crosstab query.
