SQL Server - Query to display highest marks of each subject

Hi Experts,

I have a marks table with following data (studentid, subject1, subject2, subject3)

ID  Sub1  Sub2  Sub3

1 23 45 71
2 45 78 34
3 76 23 89
4 88 35 73
5 45 78 34
6 67 78 56
7 66 75 23
8 89 45 23

 

would like to display highest marks of each subject with studentid and marks as shown below

Subject Id   Marks

sub1 8  89

sub2 2,5,6  78

sub3 3  89

 

You won't get this done with SQL.  You'll have to either write the logic in the application portion of your app (php,.net,etc.) or write a stored procedure that iterates through each row and loads a TABLE variable with the comma delimited portion of your results.