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
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.