Where condition in Pivot query in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

I want to display class and section wise result

tblStdReg: AdmissionNo,ReferNo,RollNo,SName,FName,Class,Section

tblSetMarks:AdmissionNo,Class,Section,Subject,MaxMarks,Marks

my Query is

 

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
        DECLARE @cols NVARCHAR(MAX)
        SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME([SubjectName])
        FROM (SELECT DISTINCT SubjectName FROM tblSetMarks as sm inner join tblDefSubject as ds on sm.SubjectID=ds.SubjectID) AS Course  
   
        DECLARE @qry NVARCHAR(4000)
        SET @qry =
                  N'
  
                  ;WITH cteStudentMarksDetails
                    AS ( SELECT TA.AdmissionNo
                        ,(SELECT SName FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as SName
                        ,(SELECT FName FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as FName
                        ,(SELECT SPhone FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as Phone
                         ,SUM(Marks) ObtainedMarks
                         ,SUM([MaxMarks]) TotalMarks
                         ,Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) as Percentage
                         ,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 90 THEN ''A+''
                                WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 80 THEN ''A''
                                WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 70 THEN ''B''
                                WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 60 THEN ''C''
                                WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 50 THEN ''D''
                                WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 40 THEN ''E''
                                ELSE ''FAIL'' END ) AS Grade
                         ,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 90 THEN ''OutStanding''
                                WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 80 THEN ''Excellent''
                                WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 70 THEN ''Very Good''
                                WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 60 THEN ''Good''
                                WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 50 THEN ''Satisfactory''
                                WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 40 THEN ''Work Hard''
                                ELSE ''FAIL'' END ) AS Remarks
                         FROM tblSetMarks TA
                         GROUP BY AdmissionNo
                      )
  
  
                  SELECT distinct AdmissionNo
                          ,SName
                          ,FName
                          ,Phone
                          , ' + @cols + '
                          ,ObtainedMarks
                          ,TotalMarks
                          ,Percentage
                          ,Grade
                          ,Remarks
                    FROM (SELECT AdmissionNo
                                 ,SubjectName
                                 , Marks
                                 ,(SELECT ObtainedMarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as ObtainedMarks
                                 ,(SELECT Percentage FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Percentage
                                 ,(SELECT TotalMarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as TotalMarks
                                 ,(SELECT Grade FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Grade
                                 ,(SELECT Remarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Remarks
                                 ,(SELECT SName FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as SName
                                 ,(SELECT FName FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as FName
                                 ,(SELECT Phone FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Phone
                         FROM tblSetMarks as sm
                         inner join tblDefSubject as ds
                         on sm.SubjectID=ds.SubjectID) p                 
                    PIVOT (MAX(Marks) FOR SubjectName IN ('+@cols+')) AS Pvt'
                    EXEC sp_executesql @qry

now how to show class and section wise result???

Posted one year ago Modified on one year ago

Put where condition as per your requirement.

Check with the below query.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME([SubjectName])
FROM (SELECT DISTINCT SubjectName FROM tblSetMarks as sm inner join tblDefSubject as ds on sm.SubjectID=ds.SubjectID) AS Course 
    
DECLARE @qry NVARCHAR(4000)
SET @qry =
            N'   
            ;WITH cteStudentMarksDetails
            AS ( SELECT TA.AdmissionNo
                ,(SELECT SName FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as SName
                ,(SELECT FName FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as FName
                ,(SELECT SPhone FROM tblStdReg TS WHERE TS.AdmissionNo = TA.AdmissionNo) as Phone
                    ,SUM(Marks) ObtainedMarks
                    ,SUM([MaxMarks]) TotalMarks
                    ,Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) as Percentage
                    ,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 90 THEN ''A+''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 80 THEN ''A''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 70 THEN ''B''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 60 THEN ''C''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 50 THEN ''D''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 40 THEN ''E''
                        ELSE ''FAIL'' END ) AS Grade
                    ,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 90 THEN ''OutStanding''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 80 THEN ''Excellent''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 70 THEN ''Very Good''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 60 THEN ''Good''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 50 THEN ''Satisfactory''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([MaxMarks]),1) >= 40 THEN ''Work Hard''
                        ELSE ''FAIL'' END ) AS Remarks
                    FROM tblSetMarks TA
                    GROUP BY AdmissionNo
                )   
   
            SELECT distinct AdmissionNo
                    ,SName
                    ,FName
                    ,Phone
                    , ' + @cols + '
                    ,ObtainedMarks
                    ,TotalMarks
                    ,Percentage
                    ,Grade
                    ,Remarks
            FROM (SELECT AdmissionNo
                            ,SubjectName
                            , Marks
                            ,(SELECT ObtainedMarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as ObtainedMarks
                            ,(SELECT Percentage FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Percentage
                            ,(SELECT TotalMarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as TotalMarks
                            ,(SELECT Grade FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Grade
                            ,(SELECT Remarks FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Remarks
                            ,(SELECT SName FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as SName
                            ,(SELECT FName FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as FName
                            ,(SELECT Phone FROM cteStudentMarksDetails csd Where  csd.AdmissionNo = sm.AdmissionNo) as Phone
                    FROM tblSetMarks as sm
                    inner join tblDefSubject as ds
                    on sm.SubjectID=ds.SubjectID
					--Where condition to add as per your condition for class=classid and sectionid=1
					) p                
            PIVOT (MAX(Marks) FOR SubjectName IN ('+@cols+')) AS Pvt'
EXEC sp_executesql @qry