quote:

---

The string problem wasn't discovered until today, after I uploaded some more data into the database. Before it would work in QA, but not after I loaded the data. I still think there is a problem w/ VB and the way it calls it somehow. Anywho, I'll see if I can't work toward that case of beer!!!!

Thanks,

Chris

---

quote:

---

Great, simple article, but has anyone solved the problem of using a global temp table within the procedure? Unless this is solved, multiple users could have problems suing this script. Anyone want to share an updated version of this script?

---

DROP PROCEDURE crosstab
GO
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

GO

DROP TABLE Incident 
CREATE TABLE Incident 
( 
incident_id int, 
incident_type_id int 
) 

DROP TABLE Type 
CREATE TABLE Type 
( 
type_id int, 
type_desc nvarchar(10) 
) 


INSERT INTO Incident(incident_id, incident_type_id) 
SELECT 1,1 UNION 
SELECT 2,2 UNION 
SELECT 3,2 UNION 
SELECT 4,1 UNION 
SELECT 5,3 UNION 
SELECT 6,4 UNION 
SELECT 7,5 UNION 
SELECT 8,1 

INSERT INTO Type(type_id, type_desc) 
SELECT 1, 'test1' UNION 
SELECT 2, 'test2' UNION 
SELECT 3, 'test3' UNION 
SELECT 4, 'test4' UNION 
SELECT 5, 'test5' UNION 
SELECT 6, 'test6' UNION 
SELECT 7, 'test7' UNION 
SELECT 8, 'test8' UNION 
SELECT 9, 'test9' UNION 
SELECT 10, 'test10' 

EXECUTE crosstab 'select type_desc as reported from type inner join incident on (type.type_id=incident.incident_type_id) 
group by type_desc', 'count(incident_type_id)','type_desc','type' 

create procedure CrossTab2 (@SQL varchar(1000),	
	@PivotCol varchar(100),
	@Summaries varchar(100), 
	@GroupBy varchar(100),
	@OtherFields varchar(100) = Null)
AS

set nocount on

declare @Vals varchar(5000);
set @Vals = '';

set @OtherFields = isNull(', ' + @OtherFields ,'')

exec ('SELECT Distinct convert(varchar(100),' + @PivotCol + ') as Pivot INTO ##Temp FROM (' +
	 @SQL + ') A')

select @Vals = @Vals + ', ' + 
    replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + Pivot + 
	''' THEN '),')[', ' END) as [' + Pivot )
from ##Temp order by Pivot

drop table ##Temp

set nocount off

exec ( 'select ' + @GroupBy + @OtherFields + @Vals + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy)