Hi mukesh1,
You need to use Cursor to loop through all the records and update the Max column.
Refer below sample query.
SQL
CREATE TABLE tm_QuestionSheet(QuestionNo VARCHAR(10),Maxmarks INT,marksObtained INT,Studentid INT,class_id INT)
INSERT INTO tm_QuestionSheet VALUES('AQ11','10','2','11265','6')
INSERT INTO tm_QuestionSheet VALUES('AQ12','10','4','11265','6')
INSERT INTO tm_QuestionSheet VALUES('AQ13','10','1','11265','6')
INSERT INTO tm_QuestionSheet VALUES('AQ11','10','6','11266','6')
INSERT INTO tm_QuestionSheet VALUES('AQ12','10','8','11266','6')
INSERT INTO tm_QuestionSheet VALUES('AQ13','10','9','11266','6')
SELECT QuestionNo+'/'+CAST(Maxmarks AS VARCHAR(100)) AS Question,marksObtained,Studentid
INTO #tempQuest1
FROM tm_QuestionSheet tqs
WHERE tqs.class_id = 6
DECLARE @SQLQuery2 AS NVARCHAR(MAX)
DECLARE @PivotColumns2 AS NVARCHAR(MAX)
SELECT @PivotColumns2 = ISNULL(@PivotColumns2+',','') + QUOTENAME(Question)
FROM (SELECT DISTINCT Question FROM #tempQuest1) AS PivotExample ORDER BY Question ASC
IF OBJECT_ID('dbo.##TempTableTesting2') IS NOT NULL DROP TABLE ##TempTableTesting2
SET @SQLQuery2 = N'SELECT Studentid ,' + @PivotColumns2 + ','''' AS [Max]
INTO ##TempTableTesting2
FROM #tempQuest1
PIVOT(MAX(marksObtained)
FOR Question IN (' + @PivotColumns2 + ')) AS P1'
EXEC sp_executesql @SQLQuery2
DECLARE @StudentID INT
DECLARE StudentCursor CURSOR FOR
SELECT Studentid FROM ##TempTableTesting2
OPEN StudentCursor
FETCH NEXT FROM StudentCursor INTO @StudentID
WHILE(@@FETCH_STATUS = 0)
BEGIN
DECLARE @Max INT
SELECT @Max = MAX(marksObtained) FROM #tempQuest1 WHERE Studentid = @StudentID
UPDATE ##TempTableTesting2
SET [Max] = @Max
WHERE Studentid = @StudentID
FETCH NEXT FROM StudentCursor INTO @StudentID
END
CLOSE StudentCursor
DEALLOCATE StudentCursor
SELECT * FROM ##TempTableTesting2
DROP TABLE #tempQuest1
DROP TABLE ##TempTableTesting2
DROP TABLE tm_QuestionSheet
Output
| Studentid |
AQ11/10 |
AQ12/10 |
AQ13/10 |
Max |
| 11265 |
2 |
4 |
1 |
4 |
| 11266 |
6 |
8 |
9 |
9 |
