Where condition in Pivot query in SQL Server

Last Reply 6 months ago By dharmendr

Posted 6 months 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 6 months ago Modified on 6 months 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
I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html