A dart gun/railway rifle combo is a godsend in Fallout 3 when you're dealing with a Deathclaw. Shoot it in the face with a dartgun to slow it down, then blast it with the railway rifle a few times.

The shotgun is generally the best weapon in first-person-shooters, usually because every time I play, I get all up in the enemy's face so it's harder to miss, and the shotgun is optimal for close-range fighting.

I was always disappointed to find that the Spark Shot in Resident Evil 2 isn't in any other RE game. Electricity-based weapons are always fun, but this one takes the cake.

I had this sweet pair of Great Swords in Baldur's Gate: Dark Alliance II that would start gushing blood every time an enemy came near.

I shall continue this as I think about it more...

Oddly enough, a park bench is one of the most effective weapons in Dead Rising. Just bash into a crowd with one for pleasant results. Or just mow them down with an umbrella.

The Great Knife from Silent Hill 2 was slow, but very, very satisfying, plus it's cool to use the weapon of perhaps the scariest villian in all of video games.

Other favorites:
The Plasma Rifle from Doom.
The Metal Blades and the Top Spin from the Mega Man games.
The homing missiles from Contra 3 (the one on SNES)
The microwave from Maniac Mansion (HAMSTER!)
The sandwich from Ghoul School (not many games let you throw sandwiches at giant pulsating hearts)
The ice grenades from Resident Evil 3 (like the Spark Shot, it's disappointing that this hasn't been implemented into other RE games)
The pins in World Ends With You that light enemies on fire when you press the stylus in one place.
The telekinesis in Psi-Ops.
The knife in Silent Hill: Homecoming (Alex uses it like a ninja)
The Screw Attack from the Metroid games (and not just because of the awesome name)