Total column to pivot function

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 column to pivot function

Author

Topic

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-12 : 11:53:46

Can anyone tell me if we can add a total column when using the pivot function. I want display some data for each location (being column) and add a Total column that sumup for each location.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 12:11:01

yup..you can .something like


select
*
from
(select location,col1,...
from yourtable
)m
PIVOT (SUM(col1) FOR location IN ([Loc1],[Loc2],..))p

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-12 : 12:15:02

Hi visakh this gives total for each location Loc 1 and Loc 2 as Columns but I want additional column saying Total which will be SUM(col1) for Loc1 & Loc2. I have similar query to you that display data

Loc1 Loc2
1233 215

but I want like this

Loc1 Loc2 Total
1233 215 1448

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-12 : 12:16:00

SELECT * FROM
(SELECT
'Prior FY Actuals' AS ActualHours
, AH.LocationCode
, CASE
WHEN (AH.LocationCode = BH.LocationCode)
THEN ((AH.HoursActual) + (BH.EstimatedBogie))
ELSE
((AH.HoursActual) + (PH.HoursPlan))
END AS ChargedHours
FROM @ActualHours AH
INNER JOIN @BogieHours BH ON BH.LocationCode = AH.LocationCode
INNER JOIN @PlannedHours PH ON PH.LocationCode = AH.LocationCode) AS src
PIVOT (SUM(ChargedHours) FOR LocationCode
IN ([0090B],[0160A])) AS Pvt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 12:21:15

[code]
SELECT SUM(CASE WHEN LocationCode='0090B'THEN ChargedHours ELSE 0 END) AS [0090B],
SUM(CASE WHEN LocationCode='0160A'THEN ChargedHours ELSE 0 END) AS [0160A],
SUM(ChargedHours) AS Total
FROM
(
SELECT
'Prior FY Actuals' AS ActualHours
, AH.LocationCode
, CASE
WHEN (AH.LocationCode = BH.LocationCode)
THEN ((AH.HoursActual) + (BH.EstimatedBogie))
ELSE
((AH.HoursActual) + (PH.HoursPlan))
END AS ChargedHours
FROM @ActualHours AH
INNER JOIN @BogieHours BH ON BH.LocationCode = AH.LocationCode
INNER JOIN @PlannedHours PH ON PH.LocationCode = AH.LocationCode
)t
[/code]

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-12 : 12:26:59

ty very much. I missed about crosstab. good catch of my mistake.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 12:31:26

no probs...glad that i could help

you're always welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources