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