The Shivah - It's an adventure game but it kinda has a final boss which was brilliant, the way to beat it was shown in the title screen and reinforced throughout the game and it asks you to take in all that and ask yourself: "How does a real rabbi answer questions?"

Persona 4 - You can easily end up with the bad endings but the idea behind getting the real one is great, the theme of the game is reach out to the truth, it's right there in the chorus of the battle theme, so the game constantly throws easy answers your way and to get the good ending you need to internalize the theme and reject them, instead keep looking for the truth.

These were great story moments that in other places would simply be part of a cutscene, the character would just do what it has to according to the themes and failure would never be an option. But because the games put it in the hands of the player, for better or for worse, it feels that much more potent.

mega huge spoilers , dont watch the video if you intend to play the games

1)shadow run snes , starts out great , where in you have no memories so the story builds up perfectly as you rebuild the bits and pieces of it.

2)shining force 1 :- shocking ending
http://www.youtube.com/watch?v=51Qz7IAxoW4

3)phantasy star 4 - "her last breath" this moment can break anyone playing the game
http://www.youtube.com/watch?v=61IAHniwWWw

4)metal gear solid 3 :- wish the ending was the start of the game *salutes a true patriot
http://www.youtube.com/watch?v=jzp39LizZVw

5) age of empires 2 : Joan of arc story end
http://www.youtube.com/watch?v=fP2FQLN3iwE

6) mass effect 1: wrex got killed by ashley , many may not like this one but it really made the game more awesome and gave more freedom in my choices , grunt is way awesome
http://www.youtube.com/watch?v=lHLhvRXhlDE
have got many more too huge to list

System Shock 2: http://www.youtube.com/watch?v=Q2AHuHnP8oc

Many moments in Dreamfall, like when April and Kian met each other or when Zoe visits the place where Faith lived.

Sacrifice - The entire game, especially the intro which you can see here:


http://www.youtube.com/watch?v=YNQ5yHR_3rQ


PS:T - The entire game


Diablo 2 - The cinematics

Surprised no one has said it yet.

Knights of the Old Republic - When it is revealed you are Revan.

Love the cutscene where all the quotes that hinted to it play through your mind again before the reveal. That was some god-tier plot twist shit that was.

I'm going to leave [url=http://gazoinks.blogspot.com/2012/10/video-game-storytelling-examples-of.html]here. :)

Aside from the stuff I mentioned in that article, I was actually surprised by the quality of Mark of the Ninja's story, especially the ending which casts the whole game in a different light.
That sequence is probably the most intense sequence I've experienced in a P&C game. Brilliant ending.

Maybe it's just me, but I found that plot twist incredibly obvious. I guess I've played too many "lost memories" games to not see it coming.

Morrowind.

Although not a single 'great moment', as such, the plot is so well integrated with lore and gameplay (gradually starting out completing jobs for people to find out the information, while trying to guess why, then [spoilers])

SPOILER ALERT






Perhaps the best moments for me at least (having played it first soon after Skyrim) were the sudden realisation that 'wait a minute!? You mean that...you're a dwemer?!?' and when I first gained Moon and Star. The gradual realisation that the Ordinators aren't just a generic town guard for law enforcement was also well done, and my fear on entering a Telvanni town for the first time (as a Kajiit thief who hated slavery and had robbed the Telvanni treasury to try to bankrupt them, I'd been avoiding them for most of the game) was quite genuine.

I'm going to second this. Pure dungeon crawlers normally don't appeal to me, so I was surprised at the amount of atmosphere in that game.

I was searching through the post to see if anyone mentioned Homeworld, you didn´t dissapoint, :P.

I should add, the arrival at..........home : ).

Snatchers. For all of you Cyperpunks out there.

Don't forget the crazy zealots obsessed with their nebula...

ARE YOUR OWN PEOPLE.

The fantastic cutscene in Thief, when you finally deliver the Eye to Constantin.