I have a two table structure Marks and subjects like this
| AdmissionNo |
SubjectID |
Mark |
Max |
| R-01 |
1 |
43 |
50 |
| R-01 |
2 |
35 |
50 |
| R-01 |
3 |
33 |
50 |
| R-02 |
1 |
40 |
50 |
| R-02 |
2 |
20 |
50 |
| R-02 |
3 |
10 |
50 |
| SubjectID |
SubjectName |
| 1 |
Eng |
| 2 |
Urdu |
| 3 |
Math |
I write the following query
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME([SubjectName]) from tblSetMarks as sm inner join tblAssignSubjects as si on si.ASID = sm.SubjectID INNER JOIN tblDefSubject AS dsu ON si.SubjectID=dsu.SubjectID FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')
set @query = 'SELECT AdmissionNo,' + @cols + ',Obtain,Total,Rank from
(
select fc.AdmissionNo,SubjectName,Mark
,(SELECT SUM(tsm.Mark) FROM tblSetMarks tsm where tsm.AdmissionNo = fc.AdmissionNo ) as Obtain
,(SELECT SUM(tsm.Max) FROM tblSetMarks tsm where tsm.AdmissionNo = fc.AdmissionNo ) as Total
,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS Rank
from tblSetMarks as fc
inner join tblStdReg as sr on fc.AdmissionNo=sr.AdmissionNo and Active_Status=''Active''
inner join tblSession as s on fc.SessionID=s.SessionID
inner join tblSetExam as r on fc.SetExamID=r.SetExamID
inner join tblAcademicYear as ay on ay.YearID=sr.YearID
inner join tblDefClass as dc on dc.ClassID=sr.ClassID
inner join tblDefSection as ds on ds.SectionID=sr.SectionID
inner join tblAssignSubjects as si on si.ASID = fc.SubjectID inner join tblDefSubject as dsu on si.SubjectID=dsu.SubjectID
group by fc.AdmissionNo,SName,FName,AcademicName,ClassName,SectionName,SessionName,ExamType,SubjectName,Mark
) x
pivot
(
Max(Mark)
for SubjectName in (' + @cols + ')
) p '
execute(@query)
it is showing output like this
Showing output like this
| AdmissionNo |
Eng |
Urdu |
Math |
Obtain |
Total |
Rank |
| R-01 |
43 |
Null |
Null |
111 |
150 |
2 |
| R-01 |
Null |
35 |
Null |
111 |
150 |
6 |
| R-01 |
Null |
Null |
33 |
111 |
150 |
8 |
| R-02 |
40 |
Null |
Null |
70 |
150 |
7 |
| R-02 |
Null |
20 |
Null |
70 |
150 |
8 |
| R-02 |
Null |
Null |
10 |
70 |
150 |
9 |
where as Required output should be like this
Required output
| AdmissionNo |
Eng |
Urdu |
Math |
Obtain |
Total |
Percent |
Rank |
| R-01 |
43 |
35 |
33 |
111 |
150 |
74 |
1 |
| R-02 |
40 |
20 |
10 |
70 |
150 |
47 |
2 |
I tried to write this query
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME( ISNULL(MAX(SubjectName),'')) from tblSetMarks as sm inner join tblAssignSubjects as si on si.ASID = sm.SubjectID INNER JOIN tblDefSubject AS dsu ON si.SubjectID=dsu.SubjectID FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')
set @query = 'SELECT AdmissionNo,' + @cols + ',Obtain,Total,Rank from
(
select fc.AdmissionNo,SubjectName,Mark
,(SELECT SUM(tsm.Mark) FROM tblSetMarks tsm where tsm.AdmissionNo = fc.AdmissionNo ) as Obtain
,(SELECT SUM(tsm.Max) FROM tblSetMarks tsm where tsm.AdmissionNo = fc.AdmissionNo ) as Total
,dense_rank() over(partition BY SUM(Max) order by SUM(Mark) DESC) AS Rank
from tblSetMarks as fc
inner join tblStdReg as sr on fc.AdmissionNo=sr.AdmissionNo and Active_Status=''Active''
inner join tblSession as s on fc.SessionID=s.SessionID
inner join tblSetExam as r on fc.SetExamID=r.SetExamID
inner join tblAcademicYear as ay on ay.YearID=sr.YearID
inner join tblDefClass as dc on dc.ClassID=sr.ClassID
inner join tblDefSection as ds on ds.SectionID=sr.SectionID
inner join tblAssignSubjects as si on si.ASID = fc.SubjectID inner join tblDefSubject as dsu on si.SubjectID=dsu.SubjectID
group by fc.AdmissionNo,SName,FName,AcademicName,ClassName,SectionName,SessionName,ExamType,SubjectName,Mark
) x
pivot
(
Max(Mark)
for SubjectName in (' + @cols + ')
) p '
execute(@query)
Now I want to show more column such as Rank, Percentage,Grade,ClassPosition as well.
how to do it?
