Display total rows with pivotting in t-sql

There were couple of occasions of late where people asked me way to display total rows along with detailed data while applying crosstabbing using PIVOT. Though this can be very easily generated in front end languages especially when you're using reporting tools sometime you need to get this in T-SQL especially when you're trying to export the results to excel or flat file. Thought of sharing the solution I gave here as it would benefit others who have similar requirements.

See the illustration given below


declare @t table
(
id int identity(1,1),
Category varchar(20),
Yr int,
Val int
)


insert @t (Category,Yr,Val)
select 'Cat1',2011,15 union all
select 'Cat1',2010,22 union all
select 'Cat2',2011,30 union all
select 'Cat3',2011,19 union all
select 'Cat1',2011,32 union all
select 'Cat2',2010,44 union all
select 'Cat4',2011,30 union all
select 'Cat1',2010,14 union all
select 'Cat3',2009,35 union all
select 'Cat3',2010,44 union all
select 'Cat1',2009,30 union all
select 'Cat4',2010,14 union all
select 'Cat5',2009,35 




select *
from
(
select Category,cast(Yr as varchar(4)) AS Yr,Val
from @t
union all
select Category,'Total',sum(Val)
from @t 
group by Category
)t
pivot (sum(Val) for Category IN ([Cat1],[Cat2],[Cat3],[Cat4]))p

output
---------------------------------------------------
Yr       Cat1  Cat2 Cat3 Cat4
---------------------------------------------------
2009 30  NULL  35         NULL
2010 36  44          44            14
2011 47  30          19            30
Total 113  74          98            44

As you see from above the solution is to add the totals by applying GROUP BY based on required field and union all it to the main query. This should be then used as source for applying the PIVOT over it.


EDIT: I've had another request to add a horizontal total to the above resultset to get totals corresponding to each year. The horizontal totals can be achieved with PIVOT using below small modification



select Yr,[Cat1],[Cat2],[Cat3],[Cat4],TotalYr
from
(
select Category,cast(Yr as varchar(4)) AS Yr,Val,SUM(Val) over(PARTITION BY Yr) AS TotalYr
from @t
union all
select Category,Yr,sum(Val) AS Val,totalval
from
(
select Category,'Total' AS Yr,Val,SUM(val) over () as totalval
from @t 
)p
group by Category,Yr,totalval
)t
pivot (sum(Val) for Category IN ([Cat1],[Cat2],[Cat3],[Cat4]))p
order by Yr

Now see the output

The catch here is the aggregate function SUM() applied over required group beforehand using PARTITION BY which causes the totals to be precalculated for Yr value groups before we apply the PIVOT over them based on the Category value. The total row will have similar logic used over entire dataset to get horizontal total. The SUM(val) OVER() code causes sum to be calculated over entire dataset which will give us required valued for last row.