select max data from multiple column with respect id using PIVOT in SQL SERVER

Sometimes , we face a problem , how can retrive a max data from multiple column.
suppose ,  i have a table of student result.
In this table multiple column , ie, ROLLNUMBER , MATH,PHYSICS,CHEMISTRY ,TOTALMARKS.
it can be easilly calculated what is his  TOTALMARKS , but it cam be tuff , in which subject he got maximum marks??

Try some dummy code as follow
////////////////////TABLE CREATE////////////////////////////////////////////
CREATE TABLE [dbo].[TBLRESULT]([ROLLNUMBER] [bigint] NOT NULL,[MATH] [int] NULL,[PHYSICS] [int] NULL,[CHEMISTRY] [int]
)NULL ON [PRIMARY]

//////////////////////////////INSERT DATA    START QUERY////////////////////////////////////////
INSERT INTO TBLRESULT VALUES(ROLLNUMBER,MARKSOFmaths,MARKSOFphysics,MARKSOFchemistry)

 EXAMPLE --
  INSERT  INTO TBLRESULT VALUES(1004,12,0,9)

///////////////////////////////END QUERY///////////////////////////////////////////

SELECT *  FROM TBLRESULT

ROLLNUMBER	MATH	PHYSICS	CHEMISTRY
1001	10	12	14
1002	10	14	12
1003	14	12	0

Now use this query

////////////////////////////START QUERY//////////////////////////////////////////////////////////////

select row_number()over(partition by ROLLNUMBER order by MAXMARKS desc) as MAXMARKSID,ROLLNUMBER , SUBJECTS,MAXMARKSfrom (select ROLLNUMBER,SUBJECTS,MAXMARKS from (select ROLLNUMBER,MATH,PHYSICS,CHEMISTRY from TBLRESULT) ALIAS1unpivot
(MAXMARKS for SUBJECTS in(MATH,PHYSICS,CHEMISTRY)) AS ALIAS2 ) ALIAS3


////////////////////////////////END QUERY/////////////////////////////////////////////////////

Result like this
 

MAXMARKSID	ROLLNUMBER	MAXMARKS	MARKS
1	1001	14	CHEMISTRY
2	1001	12	PHYSICS
3	1001	10	MATH
1	1002	14	PHYSICS
2	1002	12	CHEMISTRY
3	1002	10	MATH
1	1003	14	MATH
2	1003	12	PHYSICS
3	1003	0	CHEMISTRY
1	1004	12	MATH
2	1004	9	CHEMISTRY
3	1004	0	PHYSICS

Now for Mximum obtained marks subjec can be obtained as


///////////////////////////START QUERY/////////////////////////////////////////////////////////////////////////////

WITH DUMMYTABLE as (select row_number()over(partition by ROLLNUMBER order by MAXMARKS desc) as MAXMARKSID,ROLLNUMBER , SUBJECTS,MAXMARKSfrom (select ROLLNUMBER,SUBJECTS,MAXMARKS from (select ROLLNUMBER,MATH,PHYSICS,CHEMISTRY from TBLRESULT) ALIAS1unpivot
(MAXMARKS for SUBJECTS in(MATH,PHYSICS,CHEMISTRY)) AS ALIAS2 ) ALIAS3)select ROLLNUMBER,SUBJECTS,MAXMARKS from DUMMYTABLE where DUMMYTABLE.MAXMARKSID =1

////////////////////////////////////////////END QUERY//////////////////////////////////////////////////////////////////
Result like this

ROLLNUMBER	SUBJECTS	MAXMARKS
1001	CHEMISTRY	14
1002	PHYSICS	14
1003	MATH	14