Inner Joins, Coalesce and Pivot

Jes Sie

Can someone help me with this error?

Below is the code:

declare @cols varchar(1000)
declare @sqls varchar(2000)
select @cols = COALESCE(@cols + ', ','') + QUOTENAME(t.TermCode)
from tblFinalGradeDetails fg INNER JOIN tblTermMaster t ON fg.TermId = t.TermId
Group by t.TermCode
SELECT @sqls = 'select *
from (
select fg.StudentID, fg.StudentName, t.TermCode, fg.FinalGrade
from tblFinalGradeDetails fg INNER JOIN tblTermMaster t ON fg.TermId = t.TermId
WHERE fg.SchoolCode = ''ISL''
and fg.SchoolYear = ''SY201718''
) src
PIVOT (
sum(fg.FinalGrade)
FOR t.TermCode
IN (' + @cols + ')) pvt '
EXEC(@sqls)

I don't have any problems or issues without the joins but with the joins, I got that error. Thanks so much for any extended help.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tripat Bala Singh8y ago
Accepted answer
declare @cols varchar(1000)
declare @sqls varchar(2000)

select @cols = COALESCE(@cols + ', ','') + QUOTENAME(t.TermCode)
from tblFinalGradeDetails G INNER JOIN tblTermMaster t on g.TermId = t.TermId

Group by t.TermCode
SELECT @sqls = 'select *
      from (
        select StudentID, StudentName, SubjectCode, TermCode, FinalGrade
        from tblFinalGradeDetails INNER JOIN tblSubjectMaster ON tblFinalGradeDetails.SubjectId = tblSubjectMaster.SubjectId
        INNER JOIN  tblTermMaster ON tblFinalGradeDetails.TermId = tblTermMaster.TermId
        WHERE tblFinalGradeDetails.SchoolCode = ''ISL''
        and tblFinalGradeDetails.SchoolYear = ''SY201718''
        ) src
        PIVOT (
          sum(src.FinalGrade)
          FOR src.TermCode
          IN (' + @cols + ')) pvt '
EXEC(@sqls)
+1
Jes Sie8y ago
Hello Tripat, thanks for your reply. I was wondring, how to get the average of FinalGrade? I tried this code but it doesn't work:
select StudentID, StudentName, SubjectCode, TermCode, FinalGrade, AVG(FinalGrade) as Grade
        from tblFinalGradeDetails INNER JOIN tblSubjectMaster ON tblFinalGradeDetails.SubjectId = tblSubjectMaster.SubjectId
        INNER JOIN  tblTermMaster ON tblFinalGradeDetails.TermId = tblTermMaster.TermId
        WHERE tblFinalGradeDetails.SchoolCode = ''ISL''
        and tblFinalGradeDetails.SchoolYear = ''SY201718''
        GROUP BY StudentID, StudentName, SubjectCode, TermCode, FinalGrade
This code gaves me this report:
Thanks in advance.
+1
Jes Sie8y ago
I finally figured it out!
declare @cols varchar(1000)
declare @sqls varchar(2000)

select @cols = COALESCE(@cols + ', ','') + QUOTENAME(t.TermCode)
from tblFinalGradeDetails G INNER JOIN tblTermMaster t on g.TermId = t.TermId

Group by t.TermCode
SELECT @sqls = 'select *
      from (
        select StudentID, StudentName, SubjectCode, TermCode, FinalGrade
        from tblFinalGradeDetails INNER JOIN tblSubjectMaster ON tblFinalGradeDetails.SubjectId = tblSubjectMaster.SubjectId
        INNER JOIN  tblTermMaster ON tblFinalGradeDetails.TermId = tblTermMaster.TermId
        WHERE tblFinalGradeDetails.SchoolCode = ''ISL''
        and tblFinalGradeDetails.SchoolYear = ''SY201718''
        ) src
        PIVOT (
          sum(FinalGrade)
          FOR TermCode
          IN (' + @cols + ')) pvt '
EXEC(@sqls)
+1