Display records from multiple table using Common Table Expression (CTE) in SQL Server

First you need to modify Command table expression cteStudentMarksDetails to add new columns in it using subquery from tblstu table and alias it as same column name to use it in Dynamic query where you are assigning the columns from cteStudentMarksDetails for output result.

Refer the below test query and Implement it as per your logic.

SQL

CREATE TABLE #tblSetMarks(AdmissionNo VARCHAR(50), SubjectID INT,Marks INT,[Max] INT)
CREATE TABLE #tblDefSubject(SubjectID INT, SubjectName VARCHAR(20))
CREATE TABLE #tblStu(AdmissionNo VARCHAR(50),SName VARCHAR(20),FName VARCHAR(20),Phone VARCHAR(10))

INSERT INTO #tblStu(AdmissionNo ,SName ,FName ,Phone)
SELECT 'R-000267','Name 1','F Name1','9999999999'
UNION ALL
SELECT 'R-000268','Name 2','F Name2','8888888888'

INSERT INTO #tblSetMarks (AdmissionNo , SubjectID ,Marks,[Max])
SELECT 'R-000267',1,15,50
UNION ALL
SELECT 'R-000267',2,15,50
UNION ALL
SELECT 'R-000267',3,15,50
UNION ALL
SELECT 'R-000267',4,15,50
UNION ALL
SELECT 'R-000268',1,25,50
UNION ALL
SELECT 'R-000268',2,25,50
UNION ALL
SELECT 'R-000268',3,25,50
UNION ALL
SELECT 'R-000268',4,25,50  
  
INSERT INTO #tblDefSubject
VALUES (1,'English')
       ,(2,'Math')
       ,(3,'Science')
       ,(4,'Urdu')

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 #tblStu TS WHERE TS.AdmissionNo = TA.AdmissionNo) as SName
				,(SELECT FName FROM #tblStu TS WHERE TS.AdmissionNo = TA.AdmissionNo) as FName
				,(SELECT Phone FROM #tblStu TS WHERE TS.AdmissionNo = TA.AdmissionNo) as Phone
                 ,SUM(Marks) ObtainedMarks
                 ,SUM([Max]) TotalMarks
                 ,Round((SUM(Marks)) * 100/ SUM([Max]),1) as Percentage
                 ,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 90 THEN ''A+''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 80 THEN ''A'' 
                        WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 70 THEN ''B''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 60 THEN ''C''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 50 THEN ''D'' 
                        WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 40 THEN ''E''
                        ELSE ''FAIL'' END ) AS Grade
                 ,(CASE WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 90 THEN ''OutStanding''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 80 THEN ''Excellent''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 70 THEN ''Very Good''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 60 THEN ''Good''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),1) >= 50 THEN ''Satisfactory''
                        WHEN  Round((SUM(Marks)) * 100/ SUM([Max]),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
  
DROP TABLE #tblSetMarks
DROP TABLE #tblDefSubject
DROP TABLE #tblStu

Output