Super Smash Bros. Melee, Persona Q and Tales of the Abyss for me

Super Smash Bros. Melee

Xenogears. Just the impact. Play 75% of the way through the game forgetting about it and wondering what same space ship and anime characters from the future had to do with this primative RPG world.

Then when the revelation hits you in the face like a black hole shot out of a rail gun you quickly run to the nearest save spot and restart the game and watch it at least 5 times back to back completely traumatized and unable to feel anything due to being overwhelmed with that numb fuzzy glow that only the best JRPGs can provide.

Theres too many to remember.  

Most recently I've had the FF14 ARR intro ruling me.  Probably watched it over 100 times.  Now open your eyes, while our plight is repeated... still deaf to our cries, lost in hope we lie defeated...

The Kingdom Hearts instrumental one is good.

Ni no Kuni is pretty awesome too.

Gah can't remember any more but I know there are dozens :(

Fifa 2002

FF14 2.0's intro definitely deserves mentioning. I have little to offer in the way of praise for 1.0, but that cut-scene did a tremendous job of sending it off (and introducing 2.0) with a bang. As far as epic story moment go, FF14 will be hard pressed to ever top it.

From recent games Shadow Warrior reboot sticks out. Lo Wang is driving a car through bamboo forest in great mood and The Touch by Stan Bush is playing, his phone rings, he picks up and says: You got wang!

Smash Bros Brawl (that music)
The Wind Waker
Witcher 2 was very cool
WarioWare Touched was fun

Also, the Mario Sports opening cinematics on NGC were hilarious. Especialy loved the Mario Power Tennis opening.

Super Smash Bros. Melee and Fire Emblem Awakening.