Database

DECLARE @T TABLE(SName VARCHAR(20), Subject VARCHAR(20), Marks INT, ExamDate  DATE)
INSERT INTO @T
SELECT 'A', 'Subject1', 77, '2011-01-01' UNION ALL
SELECT 'A', 'Subject2', 97, '2011-01-01' UNION ALL
SELECT 'B', 'Subject1', 80 ,'2012-04-01' UNION ALL
SELECT 'B', 'Subject2', 70, '2012-03-01' UNION ALL
SELECT 'C', 'Subject1', 44, '2011-01-01' UNION ALL
SELECT 'C', 'Subject2', 90, '2011-01-01' UNION ALL
SELECT 'D', 'Subject1', 79 ,'2012-04-01' UNION ALL
SELECT 'D', 'Subject2', 66, '2012-03-01'

SELECT X.*
FROM ( SELECT
        t.*
        ,Rn = DENSE_RANK() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
FROM @T t) X WHERE X.Rn = 3

SName	Subject	   Marks	ExamDate	Rn
A	     Subject1	77	    2011-01-01	3
B	     Subject2	70	    2012-03-01	3

SELECT [Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],[Knee Care],
[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair] from 
             (
                select  ServiceLevel, ServiceId, Name
                from [Service]             
            ) x 
            pivot 
            (
                max(ServiceId)
                for Name in ([Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],
                [Knee Care],[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair])
            )
             p 

DECLARE @T TABLE(StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO @T VALUES
	('NAME1',1,300,503),
	('NAME2',2,250,500),
	('NAME3',3,378,504),
	('NAME4',4,490,500),
	('NAME5',5,399,500)

SELECT X.ID,X.TotalMarks,t1.[AggregateMarks],X.[NAME1],X.[NAME2],X.[NAME3],X.[NAME4],X.[NAME5]
FROM @T 
PIVOT 
(
    MAX(AggregateMarks)
    FOR StudentNames
    IN ([NAME1],[NAME2],[NAME3],[NAME4],[NAME5])
) AS X
JOIN @T t1 on t1.Id  = X.Id 
ORDER BY 1

CREATE TABLE #T (StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO #T VALUES
	('NAME1',1,300,503),
	('NAME2',2,250,500),
	('NAME3',3,378,504),
	('NAME4',4,490,500),
	('NAME5',5,399,500)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
 
select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames) 
                    from #T
                    group by StudentNames, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT X.ID,X.TotalMarks,t1.AggregateMarks, ' + @cols + N' from #T             
            PIVOT 
            (
                 MAX(AggregateMarks)
                 FOR StudentNames IN (' + @cols + N')
            ) AS X 	
			JOIN #T t1 on t1.Id  = X.Id 		
			ORDER BY 1'

print @query

exec sp_executesql @query

DROP TABLE #T

SELECT * INTO #Servxxx
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=YES; IMEX=1;
   Database=D:\Servxxx.xls',
   [Servxxx First Sheet$]);

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'D:\Servxxx.xls'. Make sure the object exists and that you spell its name and the path name correctly. If 'D:\Servxxx.xls' is not a local object, check your network connection or contact the server administrator.".
Msg 7303, Level 16, State 1, Line 10
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".