Super Ghouls n' Ghosts have some wicked rotating mode 7 effects on one of the later levels.
Super Tennis is quite a nice example of rotatation also when the court flips around.
Actraiser has a nice scaling function when you go into an action scene.
Mode 7 really blew my socks off as a kid when I first saw it. A real step forward. It is interesting that the Megadrive could do similar effects in games like Gunstar Heroes, but you didn't see it nearly as often.