Agree - the original query will return nothing...

What do you do if there is a tie for 5th place, i.e. two states with the same tot_marks, ranked 5th and 6th? Which one should be in the top...

I should add that if an item can appear more than once in an item composition, or in a product, then my approach will fail... This can be fixed as well,...

Chief Executive comes from Departments.Department, which cannot be (pre)added to an int. But I agree, the error message could be better.

Use convert(nvarchar, Requests.RequestID) instead. Does it change anything?

What is the datatype of Requests.RequestID and why don't you use isnull for this?

Select results into tblTest as above, then copy everything to another table (with an extra column) while populating the extra column. I don't think there is an easier way...

I thought about it at home and came up with the following. As Noel, I haven't thought much about performance. Maybe you should consider making dt1, dt2 and dt3 real...

OK, I understand what you want, it's a bit complicated

I have to leave now, I will have a look at it tomorrow, if...

Well, what I was trying to say was that I don't see how the result arrives - and that Remi is missing group by item_id in his first suggestion

Sure, thanks

Then item_id 8 should correspond to 1, shouldn't it?

Or check this thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=216410

I didn't know Remi's solution when I posted my solution, and Remi normally has the fastest solution, but just in case....

Sure, but that may not be what you want... But the columns probably have the same datatype, and then your solution is definitely preferrable.

I guess I do now

But your solution doesn't work if columnA is an int column and columnB is a varchar column... it fails if @ReportType='B'