Pivot table or operator

Question

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.