459,636 Members | 1,814 Online
Need help? Post your question and get tips & solutions from a community of 459,636 IT Pros & Developers. It's quick & easy.

# Crosstab. 2 totals ?

 P: n/a Hi, Am using robvolks crosstab-procedure to generate a crosstab query. I get this result: Total A B C juli 455 1 107 347 okt 83 1 9 73 aug 612 1 113 498 juni 451 1 108 342 So I get a total for each month. But I would also like a total of each letter Total A B C juli 455 1 107 347 okt 83 1 9 73 aug 612 1 113 498 juni 451 1 108 342 Total 1601 4 337 1260 Is that possible? /jim ---call to procedure execute crosstab 'select DATENAME(month,(theDate)) as '' '', count(*) as 'MonthsTotal'' from tblData group by DATENAME(month,(theDate))','count(letter)','letter ','tblData' -----------Robvolks procedure---- CREATE PROCEDURE crosstab @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(100), @where varchar(1000)='1=1' AS DECLARE @sql varchar(8000), @delim varchar(1) SET NOCOUNT ON SET ANSI_WARNINGS OFF SET LANGUAGE Danish EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2') EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @where + ' AND ' + @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 Dec 29 '06 #1