total column in pivot table - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

total column in pivot table

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-02-29 : 11:44:51

Hi All
I created a pivot for a table
studentid,studentname,score,assessmentname,assessmentid pulled out from several tables

if have the display
studentname, assement1, assessment2, etc...,
sam 10 20
mona 10 19
now I want to have a column at the end which calculates the total score for each student

my code for the pivot portion

SELECT @Assessments =
STUFF(
(
select ',[_' + cast(AssessmentID as nvarchar(15))
+ ']'
from GradeBook.Assessment as A
where SectionID=@SectionID
and A.Deleted=0
order by LayoutPosition

for xml path('')
),
1,1,''
)

select *
from (
SELECT Points, StudentID,
LastName +'+''', '''+ '+ FirstName as displayname, '+
'''_'''+
'+ cast( AssessmentID as nvarchar(15)) as Assessment

FROM xxx

) Data
PIVOT (
sum(Points)
FOR Assessment
IN (
' + @Assessments + '
)
) PivotTable

how to add the total dynamically.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 12:02:07

just add a column to end of select in dynamic query to get value as



STUFF(
(
select '+ [_' + cast(AssessmentID as nvarchar(15))
+ ']'
from GradeBook.Assessment as A
where SectionID=@SectionID
and A.Deleted=0
order by LayoutPosition


for xml path('')
),
1,1,''
)

and add it inside EXEC()

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources