Pivot and row totals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot and row totals

Author

Topic

DaveChapman
Starting Member

7 Posts

Posted - 2009-06-08 : 11:35:30

I have a query on pivot tables - I've searched t'internet and can't find anything that really answers my question.

Table: "Calls" with columns

ID, Cat [a,b or c], Status [1,2,3 or 4]

Pivot table query

select	Cat,
		[1] as 'start',
		[2] as 'working',
		[3] as 'closed',
		[4] as 'error'
from	(
		select Cat, Status from calls
		) c
PIVOT
	(
		Count(status) for Status in ([1],[2],[3],[4])
	) p

which returns

Cat  start       working     closed      error
---- ----------- ----------- ----------- -----------
a    3           3           1           1
b    1           4           4           2
c    1           1           0           1

This is all as I'd expect - however, I'd like to add a "total" column to the right of each row, and then also a total row to the bottom of the table, to get the result as follows:

Cat  start       working     closed      error       total
---- ----------- ----------- ----------- ----------- -----------
a    3           3           1           11          18
b    1           4           4           2           11
c    1           1           0           1            3
tot  5           8           5           14          23

Is this possible? If so, can someone help me out?

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-08 : 13:17:38

This should really be done via the reporting/presentation layer rather than in SQL.

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources