The original Metal Gear Solid has the death of the horribly mentally and physically twisted Gray Fox, crushed under the foot of Metal Gear REX. As an added bonus, you then get to repeatedly fire missiles into the face of the bastard that killed him.
Go ahead, call Revoler Ocelot's bluff about him killing Meryl if you give into the torture.
How often do you keep a dying villain company for their last minutes, and especially villains who repeatedly shot at your friend with a sniper rifle to lure you out of your hiding place? For Sniper Wolf you do, and it's unbelievably sad.
Metal Gear Solid 2 has Emma Emmerich's death. Just when you start to become attached to her, Vamp has to go and stab her. What makes it even sadder is the fact that Otacon hasn't seen her in years, and right when he gets to see her again, she dies. And then as he takes her parrot, the parrot keeps saying to him "Hal...Hal...I miss you...I miss you..."
Even more incredibly hard hitting is the "The Reason You Suck" Speech given to Raiden, by GW near the end of Metal Gear Solid 2. Even though it doesn't actually involve anyone dying, it makes a huge emotional impact by practically chewing out the player more so than the character.
In the final battle of Metal Gear Solid 3, Naked Snake has to fight his beloved mentor, The Boss, in a duel to the death. She gives a long final speech about her motives and why she is willing to sacrifice everything to fulfill her duty, and tells Snake that he has to fulfill his own. Like in most fights of the series, she's not immediately dead after being defeated and with her last words demands that Snake shoot her with her own gun. But you don't get to simply watch Snake shoot her. You have to press the fire button yourself. There is nothing you can do about it. Even worse, if you don't do it, Snake will automatically pull the trigger after a while. This directly leads to the birth of Big Boss.
The above moment is referenced in Peace Walker, where Snake rides on a horse that may or may not be the Boss's own white horse to chase the titular gear, and pushes it too hard, causing it to fall over with a broken ankle, writhing in pain. Snake has to put it down, and the cutscene flashes back and forth between Snake standing over the Horse and him standing over the Boss. Once again, you have to pull the trigger.
A cutscene in Metal Gear Solid: Portable Ops has the villain indirectly kill off the first soldier you recruited in the game, who gets a little bit of screen-time, but has almost all of his emotional connection to the player being the fact that he'd been there since the beginning.
Metal Gear Solid 4 has multiple player punches. So many, in fact, that it's possible the game will seriously injure your psyche if you're not careful. Almost everyone comes out okay in the end, but man.
But the biggest player punch of the entire franchise has to come from Metal Gear Solid: Ground Zeroes, the epilogue to Peace Walker and the prologue for Metal Gear Solid V: The Phantom Pain. We are given a Hope Spot only for it to be cruelly crushed as we see Mother Base go down in flames before Big Boss' horrified eyes, and then we see Paz, who is now on full Woobie mode, get blown up in a Heroic Suicide to save Big Boss and the survivors of MSF, which unfortunately isn't enough, as the explosion still sends his helicopter crashing down, sending him into a coma that would last nine years!
More or less completely inverted in Manhunt. The villain in the game is a disturbing sadist who compels the player character to acts of excessive, wanton violence for his own entertainment and watches the proceedings through a series of disembodied cameras. Not many games end with the player killing the personification of the part of themself that enjoys the game.
A non meta example involves Cash saving different members of his family from a group of crazed hunters, only to be told at the end of the level that their deaths would be necessary, and are killed shortly after.
In Assassin's Creed II, the first hour of the game introduces Ezio's family, and aside from the initial fight with the Pazzi, all the missions show you that the Auditore household is solid and full of love. You hang out with affable big brother Fredrico, beat up sister Claudia's cheating boyfriend, collect feathers for sickly little brother Pettruccio, and do jobs for his Reasonable Authority Figure parents Giovanni and Maria. They look like they're going to be part of the story for a while, especially considering the detailed profiles regarding them in the database. Then his father and brothers - yes, including the twelve-year-old Pettruccio - are hanged at Rodrigo Borgia's order, and his mother goes mute from shock when the guards who came for them tried to rape her. Oh, it is on now. Those Templar fuckers are going down.
Made even worse when Ezio finally confronts Rodrigo, who says he only had Ezio's brothers hung to show the Templar do not offer mercy to their enemies.
Brotherhood has Rodrigo Borgia's son Cesare kill Ezio's uncle Mario to conclude the siege of Monteriggioni. Oh, it's really fucking on now. We're not gonna spare you this time, Borgia, you had your chance, and you blew it.
The siege of Monteriggioni also laid waste to your hard work synchronizing (outside of the story) with Ezio in the second game. All the renovations, all the money spent, all the collectibles... gone in a single morning. For the split-second that it's visible, apparently not even the Armor of Altair survived (intact).
Juno forcing Desmond to stab Lucy while Desmond can do nothing but struggle and watch is possibly the worst, the fact that it comes without warning doesn't help at all.
Even worse? The player is forced to do it, as well. The cutscene cannot continue until you move the control stick. One. Step. At. A. Time. And then, you must deliver the attack with the familiar "press any button" input.
Caterina's admittance that their only night together was all politics for her definitely feels like this. The novelization actually has Ezio angsting about this for quite a bit, even contemplating abandoning it all and running away with Caterina, only for her to reiterate that she doesn't love him. Then, just to hammer it home, Desmond in the present asks what happened to her, and it's revealed that she never got her city back, then got sick and died. While this is pretty much Caterina's fate in Real Life, it still sucks.
The original Assassin's Creed introduces you to each of the targets by showing them commit an atrocity. The first brutally stabs a man to death for talking back to him, the second has a man's legs broken because he tried to flee the hospital of horrors, one throws a scholar onto a pile of burning books because the man argued (peaceably and politely) that books are treasures to be preserved, not destroyed, and another murders an innocent priest because he thinks the man might be you...the list goes on. The player is always completely unable to intervene and has to watch these terrible things happen, even though some of them would be so easy to prevent because you're literally a few yards away from the target.
Then the game punches you a second time by having a long conversation with each target in which he justifies his actions, or makes the player pity him, or etc. In short, the game takes away the first punch and in doing so makes the second death another punch.
All of them except for Majd Addin, who turns out to be a psychopath who killed people for fun.
The second punch comes at an even more heinous level than that. Each speech they give tries to justify how they're doing heinous things in pursuit of a noble cause. Now try not to think about whether you've been doing the exact same thing by killing them.
Assassin's Creed: Revelations develops the Master Assassin and Lovable Rogue, Yusuf Tazim, as Ezio's closest ally in Constantinople. His boisterous antics provide much needed comic relief for the player and the two develop an incredibly close and trusting relationship. When Ezio asks Yusuf to guard his Love Interest, Sofia, as Ezio heads off to Cappadocia to confront what he believes to be the last of the Byzantine Templars, it's not hard to see where this is going to end. Sure enough, the Big Bad threatens to pull off a Hostage For Macguffin scenario, and Ezio rushes back to Constantinople to find Yusuf killed in her defense. This inspires him (and the player) to a fury not seen since Assassin's Creed II, and what follows is a Rousing Speech to the Assassins and a Roaring Rampage of Revenge.
The Lost Archive DLC reveals that Lucy betrayed the Assassins and was leaking information to the Templars about the other Assassins, as well as being responsible for Subject 16/Clay Kaczmarek going insane and killing himself, turning that moment in the previous game into less of a punch.
Deus Ex: Human Revolution and the scene where the VTOL carrying yourself and Faridah Malik is shot down. She tells you to go on with the mission while she tries to repair the VTOL. Several possibilities result. One is that if the enemy forces are able to shoot up the VTOL because you took too long to escape then it explodes. Another is that if you escape quickly into a nearby elevator the enemy will capture her then execute her in front of you. Worse still, later in another level her butchered corpse, stripped of the augmentations is found lying on a autopsy slab. The good news is that you can avoid that fate by taking out the enemy. You can even do it using non-lethal stealth tactics.
Another strong scene is when Jensen discovers Megan Reed is still alive, and is actually willingly working with the conspiracy that faked her death that Jensen has fought and investigated so hard to unravel. To drive the point home, Jensen's cybernetic mirror shades are put on at the beginning of the game, at which point all of his reactions are usually a balance between distant professionalism and biting sarcasm, even when being empathetic. When he sees Megan, his shades come off for the first and last time in the game, as he shows genuine unrestrained human emotion at seeing her alive.