Execute dynamic pivot query with dynamic column in SQL Server

hi smile,

If you are storing class id in tblMarks table then you need to add inner join condition with Class table and have to check classid with passing variable value.

SQL

Declare @ClassId INT
SET @ClassId = 2

SELECT DISTINCT SubjectID INTO #Subject FROM tblSetMarks ORDER BY SubjectID 
                 DECLARE @cols NVARCHAR(4000) 
                 SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(SubjectID) FROM (SELECT DISTINCT SubjectID FROM tblSetMarks ) AS Course
                 DECLARE @qry NVARCHAR(4000)
                 SET @qry = 
                N'SELECT *
                FROM (
                      SELECT t.AdmissionNo,t.ReferenceNo,t.SName,t.FName,t2.Marks,t2.[SubjectID]
                      FROM tblStdReg t
                      FULL OUTER JOIN tblSetMarks t2 inner join tblDefSubject as ds on t2.SubjectID=ds.SubjectID
                      ON t.AdmissionNo = t2.AdmissionNo
                      inner join tblclass c on c.classId = t2.classId
					  and c.classId = '+@ClassId+')  p
              PIVOT(Max(Marks) FOR [SubjectID] IN (' +@cols+ ')) AS pvt '
                  
 
EXEC(@qry)
DROP TABLE #Subject