Tuesday, 25 April 2017

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

WHAT OTHERS ARE READING/RELATED TO THIS:

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

    ReplyDelete