Hmm...this is kind of a toughie for me. I'm kind of a fan of the action side of games, so my favorite titles don't tend to have many truly emotional moments to them. I guess I could scrounge a few up.
1) As has been mentioned before, the ending of HL2: Episode 2. Man, that was a punch in the gut.
2) The entire ending sequence of ICO, and indeed the general emotional presentation of that game as a whole. Traveling through this beautiful ruined castle with a mysterious girl you don't know anything about, repeatedly protecting her from shadow creatures, being separated from her, trying to go after her, and then having everything come full circle like that at the end...just awesome. Suck it, Ebert, that
3) Coincidentally enough, the introductory sequence of Shadow of the Colossus. Another set of ancient ruins, a solitary horseman placing a female body on a stone altar, a disembodied voice...the whole setup sends shivers down your spine. I really
need to finish that game one of these years.
4) It may sound silly, but the moment in the Descent 3 intro when the hulk of your loyal Pyro-GX gets dropped into that star. There's some great orchestral music playing in the background, and you can't help but think of everything that ship and you had been through. Brings a tear to my eye.
5) The scene in Beyond Good & Evil when Jade comes back to the lighthouse and finds everyone gone. The acting and the soundtrack mesh together to create a really powerful emotional moment.
6) The entire confrontation with Saavedro at the end of Myst III: Exile. Brad Dourif plays just about the most convincing crazy man I've ever seen, and watching him go through a roller-coaster of emotions in a matter of minutes was something else.
I can probably come up with one or two other things if I poke through my games collection for a bit.