how to add a grand total column to pivottable

• Sarsoura

  Ten Centuries

  Points: 1220

  More actions

  August 5, 2012 at 8:53 pm

  #260425

  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'

• Conficker

  SSCommitted

  Points: 1507

  More actions

  August 6, 2012 at 3:17 am

  #1521895

  Insert all data into Temp table and do accorndingly.

• damodharan-402092

  SSC Journeyman

  Points: 84

  More actions

  August 6, 2012 at 3:42 am

  #1521915

  Try using Pivot. Follow the link for example

  http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx