SQL Query to generate merit list for multiple colleges

Hi,
I have data of candidates, each candidate chose 15 colleges priority wise in the online admission form, after scrutiny and adding up aptitude test marks merit list should be generated for each college separately as per the priority of college selected by candidate, can anyone help me with this problem please.

Thanks

Welcome

Please provide a sample.of your data since we cant access your server and see your data.

Thank you for your reply attached the image file, note that there are 15 colleges, image shows 9

Thanks again

(upload://c4OAf4Jw4cOrfcUJocRPYOF54V0.png)

Help us help you. Please present your data not as an image (we would have to type all of that data manually)

create table #sample(appno int, applname varchar(50) ) ----etc with all the columns

insert into #sample
select 5704, 'harmain rao' union
select 5704, 'asad Rizvi'