Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to add a grand total column to pivottable Expand / Collapse
Author
Message
Posted Sunday, August 05, 2012 8:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Monday, August 06, 2012 3:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 1:56 AM
Points: 66, Visits: 361
Insert all data into Temp table and do accorndingly.
Post #1340446
Posted Monday, August 06, 2012 3:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 30, 2014 2:44 AM
Points: 6, Visits: 57
Try using Pivot. Follow the link for example

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Post #1340466
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse