But why the MAX function?

Thanks. your reply fixed my issue

Thank you. After searching a bit more i found a similar solution like yours in here

http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql

I will keep your note in mind

Thanks again

Jay

what if ax2 through ax5 is null for a record

wouldn't the case statement assign value 5 to POS?