how to add a grand total column to pivottable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » how to add a grand total column to pivottable...

how to add a grand total column to pivottable

Rate Topic

Display Mode

Topic Options

Author

Message

Sarsoura

Posted Sunday, August 05, 2012 8:53 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 11:31 AM
Points: 74, Visits: 164

I would like to add a grand total column (total number of students) to a pivot table the pivot table uses count(studentid)
DECLARE @SLOAchievement nvarchar(1000)
SELECT @SLOAchievement =
STUFF(
(
select ',[' + AchievementLevel
+ ']'
from SLO. OrgAchievementLevel
where OrgID=@OrgID

for xml path('')
),
1,1,''
)
declare @mysql varchar(1000)
set @mysql=
N'select * from
(select TP.SLOID,
tp.Studentid as studentid,
ts.SLOText,
C.achievementLevel as Achievement
from '+'#'+'TempPerc TP
cross apply
dbo.fn_NewSLO_Org_Achievement_inline('+cast(@orgID as varchar(10))+
',TP.yourpercent) as C
inner join '+'#'+
'tempSLO as Ts '+
'on
tp.SLOID=ts.SLOID
)
Data
PIVOT (
count(StudentID) FOR Achievement
IN (
' + @SLOAchievement +'
) ) PivotTable'

Post #1340328