USE [BanatDB]
GO
/****** Object: StoredProcedure [dbo].[StudentResult] Script Date: 02-02-2020 12:45:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[StudentResult]
AS
BEGIN
SELECT
Name as StudentName,
ClassName as Class,
DivisionName as Division,
SubjectName as Subject,
Salana,
Shashmahi,
CountryName as Country,
StateName as State,
DistrictName as District,
EnglishYear as English,
UrduYear as Urdu,
Village as Villagee,
StudentNo,
ClassId,
EngId,
img,
Taluka,
CityName,
SubjectCode,
MarksForAbsent
FROM
(
select
st.Name,
subj.SubjectName,
cnt.CountryName,
stat.StateName,
dst.DistrictName,
cls.ClassName,
div.DivisionName,
st.CityName,
S.ResultType,
I.ObtainMarks as ObtainMarks,
eng.Year as EnglishYear,
urdu.Year as UrduYear,
eng.EnglishYearId as EngId,
S.ClassId,
st.Village,
S.StudentNo as StudentNo,
st.Data as img,
st.Taluka as Taluka,
subj.SubjectCode,
S.MarksForAbsent
from
dbo.tblResult S
LEFT JOIN dbo.tblResultId I ON S.ResultId = I.ResultId
LEFT JOIN dbo.tblStudent st on st.StudentId=S.StudentNo
LEFT JOIN dbo.tblCountry cnt on cnt.CountryId= st.CountryId
LEFT JOIN dbo.tblState stat on stat.StateId= st.StateId
LEFT JOIN dbo.tblDistrict dst on dst.DistrictId= st.DistrictId
LEFT JOIN dbo.tblClass cls on cls.ClassId= S.ClassId
LEFT JOIN dbo.tblDivision div on div.DivisionId= S.DivisionId
INNER JOIN dbo.tblSubjectDetail subj on subj.SubjectDetailId= I.SubjectDetailId
LEFT JOIN dbo.tblEnglishYear eng on eng.EnglishYearId= S.EnglishYearId
LEFT JOIN dbo.tblUrduYear urdu on urdu.UrduYearId= S.UrduYearId
)Temp
Pivot
(
SUM(ObtainMarks) for ResultType IN (Salana,Shashmahi)
)piv order by SubjectCode
END
i also want to create columns for marksof absent along with obtainmarks how can i achive this.
Rupesh KahanePosted Feb 4, 2020, 2:13 AM