Total from pivot function - 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

General SQL Server Forums

New to SQL Server Programming

Total from pivot function

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-02 : 08:50:36

Hi

I asked this question in one of my previous posts but no response. How can I get a total value after doing a pivot operation? My code is below with results shown:


select *
 from (Select [Forecast Value], Quarter, SubRegion
	  from Pivot_DataSource) AS p
PIVOT
(SUM([Forecast Value]) FOR [Quarter] IN ([2010/1], [2010/2])) AS pvt


SubRegion 2010/1            2010/2         Total
========= ==============    ============   =====
CEE	  325632701.24	    150707348.63    ?
FRA	  163194397.66	    59379551.74     ?
GER	  411890937.069999  146646393.67
GWE	  618067524.800001  202903041.6
IBER	  207317377.57	    109555561.88
ITA	  220750621.3	    64272251.78
MEMA	  313302529.939999  153550732.82
UK&I	  424675586.739999  148575011.39

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-02 : 09:10:52

Got it:


select *, [2010/1] + [2010/2] AS Total 
 from (Select [Forecast Value], Quarter, SubRegion
	  from Pivot_DataSource) AS p
PIVOT
(SUM([Forecast Value]) FOR [Quarter] IN ([2010/1], [2010/2])) AS pvt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources