Not sure if this is quite on-topic, But Fatal Labyrinth for the Genesis/MegaDrive. It wasn't so much the fact that you died spectacularly, but the fact that gold only had one use in the game: the more you had, the better your "game over" screen funeral was. It ranged from just a rock in the wilderness to a full blown memorial complete with token crying woman. Good Times.
Moreso on topic: Here are a few that I've liked in many games:
Perfect Dark: Slayer missile directed back on yourself
Halo 3: Getting the "Tank Dropper" Achievement
Siphon Filter 2 Multiplayer: Doing a forward roll off of a ledge was always funny, since it gave you a special camera anngle every time you went to lkeap a gap.
Flashback: Running full speed off of a cliff, or into a fire.
GTA4: Jumping out of a helicopter at full altitude, especially with human passengers on multiplayer.
Super Mario World (SNES): Throwing, catching, killing enemies and other such trickery with a single shell only to unleash it upon yourself at the end of a level.