# 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

#### 1 comment:

1. 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

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