add a total to 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)

add a total to pivot table

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-04-29 : 18:03:41

Please I need help
tables
assessment (assessmentid,sectionid,assessmentitle,maxpoints)
assessmentpoint(assessmentid,studentid,score)
students(studentid,studentnumber,name)
some of the scores is null (not set yet)
I need to add a total column to the pivot table that adds only no null scores for each student in all assessments for that section
currently I am using a udf which slows down the performance

Declare @Sectionid int =3333
DECLARE @Assessments nvarchar(3000)
SELECT @Assessments =
STUFF(
(
select ',[_' + cast(AssessmentID as nvarchar(15))
+ ']'
from GradeBook.Assessment as A
where SectionID=@SectionID
and A.Deleted=0

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

DECLARE @mySQL nvarchar(4000)

declare @ParamDefinition nvarchar(50)
SELECT @mySQL =N'SELECT AP.Points, Ap.StudentID,UA.StudentNumber as ID,
UA.Name as displayname,dbo.fn_UpToNowTotal(@SectionID,AP.StudentID)as Total ,'+
'''_'''+
'+ cast(A.AssessmentID as nvarchar(15)) as Assessment
FROM GradeBook.AssessmentPoint AS AP
INNER JOIN
GradeBook.Assessment AS A
ON
A.AssessmentID = AP.AssessmentID
INNER JOIN
SP.[students] UA
ON
AP.StudentID = UA.Studentid
) Data
PIVOT (
sum(Points)
FOR Assessment
IN (
' + @Assessments + '
)
) PivotTable
order by displayname'
Set @ParamDefinition = '@SectionID int'
/* Execute the Transact-SQL String with all parameter value
Using sp_executesql Command */

Execute sp_Executesql @mySql,
@ParamDefinition,
@

sarah

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-29 : 18:45:31

can you show us the output of @mySQL ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-29 : 19:05:01

you need add a union all within main sql to include total values before you apply pivot over them.

see similar logic used below

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=173915

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources