SQL Server: Find Max for each row in Pivot Table

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