April 29, 2012 at 3:24 pm
Please I need help
I have the following 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,
@SectionID
April 30, 2012 at 8:29 am
Please read the first link in my signature about best practices for posting questions. We need ddl, sample data and desired output. In this case we probably also need ddl for that function, although I suspect you will not want to use it (scalar functions are notoriously slow!!!!).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply