SQL Pivot Tricky Query Example Asked in TCS
1). We have a table(#Temp1) contain RollNumber, Subject, Marks and want output as below, Write query for same.(pivot) asked in TCS
SELECT * FROM (SELECT * FROM(SELECT RN,[Subject],Marks from #Temp1)a
PIVOT(MAX(Marks) for [Subject] in ([Math],[Hindi],[English]))E) K
CROSS APPLY (SELECT MAX(Marks) MaxMarks FROM #temp1 WHERE RN = K.RN) S
2). Now write query which will show Subject as well like below .(pivot)
SELECT * FROM (
SELECT * FROM( SELECT RN,[Subject],Marks from #temp1)a
PIVOT (MAX(marks) for [Subject] in ([Math],[Hindi],[English]))E) K
CROSS APPLY (
SELECT CONCAT(Marks,('-'+[Subject])) MaxMarks_Subject FROM (
SELECT ROW_nUMBER() OVER(PArtition by rn order by Marks DESC) RowNum,Marks,Subject FROM #temp1 Where RN= K.RN)
J WHERE J.RowNum = 1
) S
SELECT * FROM (SELECT * FROM(SELECT RN,[Subject],Marks from #Temp1)a
ReplyDeletePIVOT(MAX(Marks) for [Subject] in ([Math],[Hindi],[English]))E) K
CROSS APPLY (SELECT MAX(Marks) MaxMarks FROM #temp1 WHERE RN = K.RN) S
SELECT * FROM (
SELECT * FROM( SELECT RN,[Subject],Marks from #temp1)a
PIVOT (MAX(marks) for [Subject] in ([Math],[Hindi],[English]))E) K
CROSS APPLY (
SELECT CONCAT(Marks,('-'+[Subject])) MaxMarks_Subject FROM (
SELECT ROW_nUMBER() OVER(PArtition by rn order by Marks DESC) RowNum,Marks,Subject FROM #temp1 Where RN= K.RN)
J WHERE J.RowNum = 1
) S