I was just talking to my GF about this today. We were trying to think of any other endings that were as shocking or powerful as this one.
MGS 3 had a great plot twist at the end, and was pretty sad.
Star Wars KOTOR's final chapters were pretty damn good. While the Truth you learn isn't at the game's end, it does set the pace for the final chapter.
Witcher 2's final boss fight comes to mind too. By the time I followed Letho through the whole game and dealt with everything the world was going through, I wasn't even sure if I wanted to kill him anymore.