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'