OK well, just finished based on how you are selecting at the moment (i.e. earliest months first for same values)
what you need to do is to make another table (I've called it myTable2) from the existing table - and note that each row in the original table needs an autonumber primary key (I've called it PK) so reocrds can be matched exactly.
Note if
1. all your values were unique for a given service
2. or you didn't mind having '3rd equal' spread over (in the example quoted) months 4, 6 and 10 as well
3. or you just wanted to know the top 5
it would be much, much simpler and probably would not need another table made!
Use this SQL to make the table - the bit in green calculates the rank.
Code:
SELECT myTable.PK, myTable.ServType, myTable.Service, myTable.ServMth, myTable.PCents, Switch([PK] In (SELECT TOP 1 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth),1,[PK] Not In (SELECT TOP 1 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth) And [pk] In (SELECT TOP 2 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth),2,[PK] Not In (SELECT TOP 2 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth) And [pk] In (SELECT TOP 3 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth),3,[PK] Not In (SELECT TOP 3 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth) And [pk] In (SELECT TOP 4 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth),4,[PK] Not In (SELECT TOP 4 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth) And [pk] In (SELECT TOP 5 PK FROM myTable As T WHERE Service=myTable.Service ORDER BY PCents DESC, servmth),5) AS Rank INTO mytable2
FROM myTable
then a simple crosstab, similar to the first one (as a process, you could run the first crosstab off the new table instead since it has the same percent values)
Code:
TRANSFORM First(myTable2.Rank) AS FirstOfRank
SELECT myTable2.ServType, myTable2.Service
FROM myTable2
GROUP BY myTable2.ServType, myTable2.Service
ORDER BY myTable2.ServMth
PIVOT myTable2.ServMth