Column Total using Pivot 1

[kingstonr]

HI I am using the below query and getting the result.
select name,
Totalfor2006=isnull([2006],0),
Totalfor2005=isnull([2005],0)
from #temp
pivot
(
Sum(amt)
for [yr] in ([2006],[2005])
) as P

Name Totalfor2006 Totalfor2005
---------------------------------
x 100 200
Y 10 20

Now I need Column Total as below with the above query.
can anybody help me out in this.


Name Totalfor2005 Totalfor2005 <b>Total</b>
------------------------------------------
x 100 200 300
Y 10 20 30

 

[SQLDenis]

create table #temp([yr] int,amt int, name varchar(50))
insert #temp
select 2006,100,'x' union all
select 2006,10,'y' union all
select 2005,200,'x' union all
select 2005,20,'y' 

select name,
Totalfor2006=isnull([2006],0),
Totalfor2005=isnull([2005],0),isnull([2006],0) + isnull([2005],0) as Total
from #temp
pivot
(
Sum(amt) 
for [yr] in ([2006],[2005])
) as P


drop table #temp

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

and another way

create table #temp([yr] int,amt int, name varchar(50))
insert #temp
select 2006,100,'x' union all
select 2006,10,'y' union all
select 2005,200,'x' union all
select 2005,20,'y' 

select p.name,
Totalfor2006=isnull([2006],0),
Totalfor2005=isnull([2005],0),Total
from #temp
pivot
(
Sum(amt) 
for [yr] in ([2006],[2005])
) as P
join (select name,sum(amt) as Total from #temp
group by name) z on p.name = z.name

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/