May 20, 2012 at 8:34 pm
I have a pivot table that displays
studentid and a list of assessments as the columns
the cells are the scores of those students in those assessments
I would like to add a column which is the total scores for each student
declare @Sectionid int=1984
DECLARE @Assessments nvarchar(max), @Total nvarchar(max)
SELECT @Assessments =
STUFF(
(
select ',' + quotename(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)
SELECT @mySQL =
N'
select *
from (
SELECT AP.Points, Ap.StudentID,
U.lastname +'+''', '''+ '+U.firstname as displayname,'+
'+ 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.[User] U
ON
AP.StudentID = U.UserID
) Data
PIVOT (
sum(Points)
FOR Assessment
IN (
' + @Assessments + '
)
) PivotTable
order by displayname'
exec (@mysql)
thanks in advance
May 20, 2012 at 10:01 pm
Sarsoura (5/20/2012)
I would like to add a column which is the total scores for each student
This is one of the many reasons why I prefer CROSS TABs to PIVOTs. Please see the following for how to do both.
http://www.sqlservercentral.com/articles/T-SQL/63681/
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2012 at 9:12 am
Formatting:
declare @Sectionid int=1984
DECLARE @Assessments nvarchar(max), @Total nvarchar(max)
SELECT @Assessments =
STUFF(
(
select ',' + quotename(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)
SELECT @mySQL = N'
select *
from (
SELECT AP.Points, Ap.StudentID,
U.lastname +'+''', '''+ '+U.firstname as displayname,'+
'+ 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.[User] U
ON AP.StudentID = U.UserID
) Data
PIVOT (
sum(Points) FOR Assessment
IN (' + @Assessments + ' )
) PivotTable
order by displayname'
exec (@mysql)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 21, 2012 at 9:14 am
I'm with Jeff on it: you better use CROSS TAB.
May 21, 2012 at 1:58 pm
Jeff Moden (5/20/2012)
Sarsoura (5/20/2012)
I would like to add a column which is the total scores for each studentThis is one of the many reasons why I prefer CROSS TABs to PIVOTs. Please see the following for how to do both.
Personally I like to use SSRS to build reports for this sort of stuff.... or use SSAS to represent it. 🙂
Though your cross tab in SQL is dang cool... Making SQL do this stuff just always feels wrong to me 🙂
May 21, 2012 at 3:13 pm
mtassin (5/21/2012)
Jeff Moden (5/20/2012)
Sarsoura (5/20/2012)
I would like to add a column which is the total scores for each studentThis is one of the many reasons why I prefer CROSS TABs to PIVOTs. Please see the following for how to do both.
Personally I like to use SSRS to build reports for this sort of stuff.... or use SSAS to represent it. 🙂
Though your cross tab in SQL is dang cool... Making SQL do this stuff just always feels wrong to me 🙂
I suppose you'd really have a hard time if I showed you how to format and conditinally colorize values in the CROSS TAB and email it. 🙂 Or, how about making a popup in T-SQL to ask the user a question before proceeding in a stored procedure? How about T-SQL to create a PDF directly?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2012 at 1:02 pm
This is just an option that came to my mind ...
declare @Sectionid int=1984
DECLARE @Assessments nvarchar(max), @Total nvarchar(max)
SELECT @Assessments =
STUFF(
(
select ',' + quotename(cast(AssessmentID as nvarchar(15)))
from GradeBook.Assessment as A
where SectionID=@SectionID
and A.Deleted=0
for xml path('')
),
1,1,''
)
DECLARE @AssessmentsSumExpression nvarchar(max)
SELECT @AssessmentsSumExpression =
STUFF(
(
select '+ ISNULL(' + quotename(cast(AssessmentID as nvarchar(15)))+',0) '
from GradeBook.Assessment as A
where SectionID=@SectionID
and A.Deleted=0
for xml path('')
),
1,1,''
)
DECLARE @mySQL nvarchar(4000)
SELECT @mySQL =
N'
select *, Total = '+@AssessmentsSumExpression+'
from (
SELECT AP.Points, Ap.StudentID,
U.lastname +'+''', '''+ '+U.firstname as displayname,'+
'+ 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.[User] U
ON
AP.StudentID = U.UserID
) Data
PIVOT (
sum(Points)
FOR Assessment
IN (
' + @Assessments + '
)
) PivotTable
order by displayname'
exec (@mysql)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply