Crosstab. 2 totals ?

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
GOJim Andersen (jba020@.politiSLET.dk.invalid) writes:

Quote:

Originally Posted by

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?

You would use INSERT EXEC to capture the result from the crosstab
procedure into a temp table, and then compute a total row from the
data in it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;

But why not use a report writer in the front end, like you are supposed
to?|||--CELKO-- wrote:

Quote:

Originally Posted by

SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;
>
But why not use a report writer in the front end, like you are
supposed to?

So I don't have to hardcode my A, B and C's ?
Because I am using Visual Studio .NET and that leaves me with Crystal
Reports (yuckk, hark, spit) as a reporting tool.

I think I will try Erlands suggestion.

/jim