Hi Sean Lange,
Thanks for correcting me for pronounce the name and I am sorry or my mistake on it. Since, I am an Oracle DBA, and actually this question is related to my friend who is SQL Server DBA. I posted the same question on Oracle Forum too, because I am really very poor in SQL, so first I posted here and then tried to see the solution how it will work in Oracle, so that I can try to see its flow in SQL Server, but again my lack of knowledge failed to achieve the required output.
I am really Thankful to you and Luis Cazares for great replies.
But, I don't know why I am getting below error when I ran your way :
Server: Msg 209, Level 16, State 1, Line 27
Ambiguous column name 'rollno'.
If you and other members allow me to post my friend's question which are only related to version 2000, I will learn SQL and my friend will follow me.